Billing users

We have a mssql hotel with our clients databases of various sizes. Our problem is our to bill these clients in a fair way to cover the total cost of maintaining the database server. I will appreciate any suggestion. Thanks in advance!
There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!
|||Thanks very much Brian and have a happy easter!

Billing users

We have a mssql hotel with our clients databases of various sizes. Our problem is our to bill these clients in a fair way to cover the total cost of maintaining the database server. I will appreciate any suggestion. Thanks in advance!There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!|||Thanks very much Brian and have a happy easter!

Billing users

We have a mssql hotel with our clients databases of various sizes. Our probl
em is our to bill these clients in a fair way to cover the total cost of mai
ntaining the database server. I will appreciate any suggestion. Thanks in ad
vance!There aren't charge back mechanisms built in to SQL Server as you know.
You'll need to do the work based on sampling of some kind and come up with a
formula that you think is decent for your needs.
Take a look at the columns in master..sysprocesses and see what's of
interest to bill by.
Some people I've chatted with bill primarily based on IO and IO is easy to
track on a file level by using fn_virtualfilestats.
You would think that tracking by IO is not representative. But in many ways,
disk is the most expensive part of most server configurations and it's easy
to track on a pretty reliable way with the above function...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Bamyz" <anonymous@.discussions.microsoft.com> wrote in message
news:D6BBBA2F-0274-40CB-BF9B-5EB4667C376D@.microsoft.com...
> We have a mssql hotel with our clients databases of various sizes. Our
problem is our to bill these clients in a fair way to cover the total cost
of maintaining the database server. I will appreciate any suggestion. Thanks
in advance!|||Thanks very much Brian and have a happy easter!

Big Log file that won't shrink....

I have a fairly simple DB, One Log file, one data file, its got half a dozen tables in it that are used for a web based app that allows users to look each other up (think online telephone directory with a few extra bits and you are there), there's a data feed that adds new lines each day as users data changes or new people get added. But this is a couple of hundred or so lines a day, the tables hold about 40,000 data lines as we never delete data just retire it.

The problem is the main data file is about 200Mb, the Log file however is 3.8Gb. Now we did import and re-import various bits initially and clear down tables and such. But I don't understand why i can't shrink the log file. I've tried everything I can think of. I've even detached and re-attached the DB. Still no joy.

The file size/usage is

data file 200Mb size in use 117Mb

log file 3749.99Mb size in use 3719.89Mb

Anyone have any suggestions for things to look at?

many thanks

Steve

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2471

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

bidirectional snapshot replication?

I currently have a list of users in a SQL 2005 database. Users are
authenticated based on their email address, and each user has a number
of areas they do and don't have access to. A website is used to add
users to the database, or to add area permissions for an exisiting
user.
I would like to set up a number of replicated servers around the world.
At least one would have very high latency and very low bandwidth to it.
All Sites are connected by VPN.
My question is: Is there a way to do bidirectional replication between
all of the servers? All servers would ideally be both publishers and
subscribers.. If so, which type of replication would you use? I was
thinking snapshot replication twice a day, but that may not make
sense..
Thanks in advance..
Have a look at peer-to-peer transactional replication - this sounds like
what you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

BI- Collaboration and reports

I would like to get ideas on how how to enable report users not only
just view reports but also take action after viewing a report by
collaborating with collegues. Here's a use case-
- A project mangager (PM) logs into a portal and view a projects
status report listing current status of all projects managed by him.
- He finds that one of the projects is falling behind due to a pending
task that Peter is working on.
******Collaboration piece******
- In the portal itself the PM enter in a comment for Peter to clarify
the task's status.
- Peter logs into the portal and sees the PMs' comments. In response
he adds a comment that his task completion is dependent on the the
CEO approving a pending PO.
- PM logs into the portal opens the same report and sees Peters
comments. He updates his weekly project issues report to highlight
this dependency.
I have seem Microsoft Performance Point demos that exibit similar
collaboration features. I am not sure if the collaboration piece
requires Performance Point or is it a Sharepoint feature. Any ideas on
how to implement, what technologies to use would be appreciated.
Regards,
KenIt is as you say a feature of PerformancePoint Server.
You could also check out Analyzer by StrategyCompanion.
"Ken" <raidken@.yahoo.com> wrote in message
news:3dfe603c-e258-40c4-939a-8405951dbcd7@.p73g2000hsd.googlegroups.com...
>I would like to get ideas on how how to enable report users not only
> just view reports but also take action after viewing a report by
> collaborating with collegues. Here's a use case-
> - A project mangager (PM) logs into a portal and view a projects
> status report listing current status of all projects managed by him.
> - He finds that one of the projects is falling behind due to a pending
> task that Peter is working on.
> ******Collaboration piece******
> - In the portal itself the PM enter in a comment for Peter to clarify
> the task's status.
> - Peter logs into the portal and sees the PMs' comments. In response
> he adds a comment that his task completion is dependent on the the
> CEO approving a pending PO.
> - PM logs into the portal opens the same report and sees Peters
> comments. He updates his weekly project issues report to highlight
> this dependency.
> I have seem Microsoft Performance Point demos that exibit similar
> collaboration features. I am not sure if the collaboration piece
> requires Performance Point or is it a Sharepoint feature. Any ideas on
> how to implement, what technologies to use would be appreciated.
> Regards,
> Ken|||Ken,
If this already exists in your portal you can add hyper links to you report.
AK Code Rat.
"Ken" wrote:
> I would like to get ideas on how how to enable report users not only
> just view reports but also take action after viewing a report by
> collaborating with collegues. Here's a use case-
> - A project mangager (PM) logs into a portal and view a projects
> status report listing current status of all projects managed by him.
> - He finds that one of the projects is falling behind due to a pending
> task that Peter is working on.
> ******Collaboration piece******
> - In the portal itself the PM enter in a comment for Peter to clarify
> the task's status.
> - Peter logs into the portal and sees the PMs' comments. In response
> he adds a comment that his task completion is dependent on the the
> CEO approving a pending PO.
> - PM logs into the portal opens the same report and sees Peters
> comments. He updates his weekly project issues report to highlight
> this dependency.
> I have seem Microsoft Performance Point demos that exibit similar
> collaboration features. I am not sure if the collaboration piece
> requires Performance Point or is it a Sharepoint feature. Any ideas on
> how to implement, what technologies to use would be appreciated.
> Regards,
> Ken
>

Between [Enter the Start Date] And [Enter the End Date])

Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @.Enter_Date but I need them to be able to enter a start date and a end date
But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'.

What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??

SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]Is it possible for your front end to ask for the inputs? Then you could just supply both dates as parameters to a sproc.|||My front end is an Access 2003 an MDB, I need to change it to a ADP but Jet SQL and MSSQL dont exactly speak the same language|||Sure they do, I use pass-through queries all the time.

I use code to get my parameter values from the user, and then change the
QueryDef.SQL on the fly.

Start a new query, then go to Query -> Sql Specific -> Pass-Through

Then for your SQL
Execute YourSproc "paramvalue1", "paramvalue2"|||YESSSS

SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
ORDER BY [IR Number]

I'M SO HAPPY IT WORKS FINALLY HOOOORRAAAAYYYYY

HAPPY DANCE!!!!!!!!!!!!!!!!!!!!!!

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications? I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!
"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications? I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.
|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.p hx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pas
s
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)

> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldnt code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)

> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:Olhu%23%23
0eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> There you are :-)
>
> I wouldnt code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to secure an exposed SQL Server

Hi experts,
I have an application that my company designed and sold to users years ago
that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
you here!).
There is basically a straight NAT statement from our firewall to the SQL
server that allows ALL inbound traffic to SQL. I am looking for the best
method to secure this connection from the application side (it is under
rewrite). Possibly encode the app to use some sort of VPN or maybe add a
front end server to autheticate the SQL users first, change the port and pass
to the back-end sql server?... I dont know really... can someone please
provide me with some design suggestions to take to my developers in order to
secure and encrypt there SQL sessions for their appications' I am able to
design hardware solutions to assist with this too!
I know its best practices to NOT have SQL open, but this late in the game,
it would take a miracle to get all our customers to change ports. Thanks for
your timely suggestions!!"Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
> Hi experts,
> I have an application that my company designed and sold to users years ago
> that requires SQL port 1433 to be open to the Internet. (Insert .. shame a
> you here!).
There you are :-)
> There is basically a straight NAT statement from our firewall to the SQL
> server that allows ALL inbound traffic to SQL. I am looking for the best
> method to secure this connection from the application side (it is under
> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
> front end server to autheticate the SQL users first, change the port and
> pass
> to the back-end sql server?... I dont know really... can someone
> please
> provide me with some design suggestions to take to my developers in order
> to
> secure and encrypt there SQL sessions for their appications' I am able to
> design hardware solutions to assist with this too!
I wouldn´t code that on my own, I would suggest using a software VPN client
which establishs a conection to the internal network and use the SQLServer
the old fashioned way. Exposing the SQLerver is always risky because your
are exposing productional data to the internet and to possible hackers. Even
if you are coding of 99% solution, that would bring nightmares if I would be
responsible for that.
SO my suggestion would be to use a hardware solution on the one sideand a
software / Hardware solution on the other side implementing VPN (perhaps, if
you have money left to implement some securiyt with some kind of external
certification /smartcard solution)
> I know its best practices to NOT have SQL open, but this late in the game,
> it would take a miracle to get all our customers to change ports. Thanks
> for
> your timely suggestions!!
Just my two cents for that.
HTH, Jens Suessmeyer.|||We use a hardware firewall to only let some specific IP to access the
SQLServer through the Internet, until now, it is fine.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:Olhu%23%230eFHA.1404@.TK2MSFTNGP09.phx.gbl...
> "Scott" <Scott@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:CE23D173-4B46-4AB3-B139-4664E65B58B3@.microsoft.com...
>> Hi experts,
>> I have an application that my company designed and sold to users years
>> ago
>> that requires SQL port 1433 to be open to the Internet. (Insert .. shame
>> a
>> you here!).
> There you are :-)
>> There is basically a straight NAT statement from our firewall to the SQL
>> server that allows ALL inbound traffic to SQL. I am looking for the best
>> method to secure this connection from the application side (it is under
>> rewrite). Possibly encode the app to use some sort of VPN or maybe add a
>> front end server to autheticate the SQL users first, change the port and
>> pass
>> to the back-end sql server?... I dont know really... can someone
>> please
>> provide me with some design suggestions to take to my developers in order
>> to
>> secure and encrypt there SQL sessions for their appications' I am able
>> to
>> design hardware solutions to assist with this too!
> I wouldn´t code that on my own, I would suggest using a software VPN
> client which establishs a conection to the internal network and use the
> SQLServer the old fashioned way. Exposing the SQLerver is always risky
> because your are exposing productional data to the internet and to
> possible hackers. Even if you are coding of 99% solution, that would bring
> nightmares if I would be responsible for that.
> SO my suggestion would be to use a hardware solution on the one sideand a
> software / Hardware solution on the other side implementing VPN (perhaps,
> if you have money left to implement some securiyt with some kind of
> external certification /smartcard solution)
>> I know its best practices to NOT have SQL open, but this late in the
>> game,
>> it would take a miracle to get all our customers to change ports. Thanks
>> for
>> your timely suggestions!!
> Just my two cents for that.
> HTH, Jens Suessmeyer.
>

Best way to replicate Laptop users

Hi,
I have a central server witch fetches information from other local
servers. I setup merge replications so that all changes made at local
server would be replicated at central server in the night.
Now, I want all the information on central server (that is, the
combined information from all the local servers) to be dumped on the
laptop users so that they can work offline. Because of the amount of
the data I have to transfer, I can't no longer just delete all the
objects and copy over all tables any more (takes way too long.)
However, I can not setup another merge replication because the central
server is already setuped in the local to central merge replication.
The only way I could think of is to setup merge replication from local
servers to the laptop user and probably setup a job to setup the first
snapshot dump (takes around 45 minutes) in the middle of the night so
that it won't affect local server during business hour.
I am just wondering if there are better ways to do this?
Conditions:
1 - I prefer not to touch the local servers since they are vital to our
operation during regular hours (9am ~ 9pm, 7 days / week).
2 - I prefer to replication with the central server for it is a faster
server and it is not linked to any real-time operation machines (except
that it replicates with the other local servers in the middle of the
night)
3 - Snapshot replication or DTS is out of the question for the sales
table contains 2 million+ rows and growing
Any good suggestions?
Thank you in advance
Charlie Chang
[charlies224@.hotmail.com]
This might be a good case for using dynamic snapshots so the laptop users
get only the data they require. You will need a column on the tables you are
replicating to partition your data with.
I would use WSM (Windows Synchronization Manager) to manage the pull.
Depending on your data flow you might be able to publish from the local
servers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<charlies224@.hotmail.com> wrote in message
news:1109629058.527708.180340@.l41g2000cwc.googlegr oups.com...
> Hi,
> I have a central server witch fetches information from other local
> servers. I setup merge replications so that all changes made at local
> server would be replicated at central server in the night.
> Now, I want all the information on central server (that is, the
> combined information from all the local servers) to be dumped on the
> laptop users so that they can work offline. Because of the amount of
> the data I have to transfer, I can't no longer just delete all the
> objects and copy over all tables any more (takes way too long.)
> However, I can not setup another merge replication because the central
> server is already setuped in the local to central merge replication.
> The only way I could think of is to setup merge replication from local
> servers to the laptop user and probably setup a job to setup the first
> snapshot dump (takes around 45 minutes) in the middle of the night so
> that it won't affect local server during business hour.
> I am just wondering if there are better ways to do this?
> Conditions:
> 1 - I prefer not to touch the local servers since they are vital to our
> operation during regular hours (9am ~ 9pm, 7 days / week).
> 2 - I prefer to replication with the central server for it is a faster
> server and it is not linked to any real-time operation machines (except
> that it replicates with the other local servers in the middle of the
> night)
> 3 - Snapshot replication or DTS is out of the question for the sales
> table contains 2 million+ rows and growing
>
> Any good suggestions?
> Thank you in advance
> Charlie Chang
> [charlies224@.hotmail.com]
>

best way to log out when running the job

Hi,
When I run the job, I am not sure whether it could be done at the table
level to log users out. I would not want to keep them out of the whole
database...only the tables I are updating. Also, once the job is running, I
would like to prevent any users from accessing the tables I am updating unti
l
the end of the job.
Is there anyway to do that?
Thanks
EdYou can take an Exclusive lock on the table and hold it until you are done.
That will not allow anyone to access the table as they will be blocked. Not
sure if they will like you for doing that though<g>.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
> Hi,
> When I run the job, I am not sure whether it could be done at the table
> level to log users out. I would not want to keep them out of the whole
> database...only the tables I are updating. Also, once the job is running,
> I
> would like to prevent any users from accessing the tables I am updating
> until
> the end of the job.
> Is there anyway to do that?
> Thanks
> Ed|||Andrew,
Thanks for your answer. I would also like to know what is the best way to
log users out before the job is running. Again, I only need to log them out
of the tables that I will be updating not the whole database.
Thanks again
Ed
"Andrew J. Kelly" wrote:

> You can take an Exclusive lock on the table and hold it until you are done
.
> That will not allow anyone to access the table as they will be blocked. N
ot
> sure if they will like you for doing that though<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
>
>|||You can't "Log out" users from a table. They log in to the server and
potentially have access to one or more db's in that server. A user only
takes out locks while they are accessing the data. So any locks they may
have on any specific table are released once they are done with the query or
the transaction is committed or rolled back. Why do you care if they are in
the table or not? Maybe if you explain in more detail what you are
attempting to do we can suggest an appropriate action.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
> Andrew,
> Thanks for your answer. I would also like to know what is the best way
> to
> log users out before the job is running. Again, I only need to log them
> out
> of the tables that I will be updating not the whole database.
> Thanks again
> Ed
> "Andrew J. Kelly" wrote:
>|||The major reason why I would like to "Log Out" users is because the job has
to be run in the daytime and the job has to be finished as quick as
possible. with so many users using the resources of the server (hugh data
warehouse to be accessed by so many people), I would like to "Log Out" all
users and block the access of all updating tables except the one running by
the job.
any suggestion
Thanks
Ed
"Andrew J. Kelly" wrote:

> You can't "Log out" users from a table. They log in to the server and
> potentially have access to one or more db's in that server. A user only
> takes out locks while they are accessing the data. So any locks they may
> have on any specific table are released once they are done with the query
or
> the transaction is committed or rolled back. Why do you care if they are
in
> the table or not? Maybe if you explain in more detail what you are
> attempting to do we can suggest an appropriate action.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
>
>|||Well you can KILL a connection that has a lock in the table you want but
that obviously ruins their day. Other than that there is no way to remove a
users locks on a table until that user has finished accessing it and
committed or rolled back their transaction if they had one. As I stated
earlier the only real way to prevent them from accessing the table is to
take out an exclusive lock. You may have to wait a while until all current
users in that table have finished before the lock takes hold. But no new
users can access it either. It almost sounds like you need to place the db
in a single user mode, do the work and return it as soon as possible. You
can specify a termination option to kick out the current users after a
period of time with the alter database command.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:5C6CCABD-739B-472F-892B-4F3B3F53DB31@.microsoft.com...
> The major reason why I would like to "Log Out" users is because the job
> has
> to be run in the daytime and the job has to be finished as quick as
> possible. with so many users using the resources of the server (hugh data
> warehouse to be accessed by so many people), I would like to "Log Out" all
> users and block the access of all updating tables except the one running
> by
> the job.
> any suggestion
> Thanks
> Ed
> "Andrew J. Kelly" wrote:
>