Run SQL script conditional on logins & disable logins while script run

[AnnaWnz]

Hi

I am after a couple of bits of help:

1. I want to run a script, which is initiated manually, but it needs to check the Activity table firs to ensure that there are no users logged in, and if there are, then the script won't run and the user will be informed. I know how to find out if there are users in the database or not (using Select Count(*) from Activity where if count is >0 then a user is logged in), but I am not sure how to incorporate that into a conditional statement in teh SQL script.

2. While the script is running, I want to be able to stop any users logging in.

Thanks for any help.

Anna

 

[SemperFiDownUnda]

check out "Setting Database Options" in BOL and look at RESTRICTED_USER

Basically you need to issue a ALTER DATABASE statement. You can elect to put the database into RESTRICTED_USER mode but not terminate exsisting connections if you wish.

 

[IDDQD]

Hi
for the first question try this

DECLARE @usercount INT

SELECT @usercount = COUNT(*) FROM Activity

IF @usercount > 0
BEGIN
-- your script
ELSE
--- notify user
END