Detaching and clearing out all users 1

[LonnieJohnson]

Is there another command or additional syntax to use if you want to detach a database and clear out any users that are in the database?

I am using this in a stored proc...

EXEC sp_detach_db 'clientDb', 'true'

I get the message that it cannot detach because it is currently in use.

When doing this via enterprise manager there is an option to clear them out. How do I do that with T-SQL?

Thanks.


ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[SQLDenis]

you could use

Alter database clientDb set single_user with Rollback immediate


then detach


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

You can also loop thru all the users and KILL them

like this, just make sure you are not connected to this DB while running it (from the same window) since it won't let you kill your own connection

DECLARE @dbname sysname
SELECT @dbname = 'clientDb'
DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)

OPEN loop_name
DECLARE @conn_id SMALLINT
DECLARE @exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @conn_id
WHILE (@@fetch_status = 0)
BEGIN
SELECT @exec_str = 'KILL ' + CONVERT(VARCHAR(7), @conn_id)
EXEC( @exec_str )
FETCH NEXT FROM loop_name INTO @conn_id
END
DEALLOCATE loop_name

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

I suggest also stopping the SQLServerAgent service. Any JOB that is running is also a user/connection. In addition to that, any connection YOU have is a user/connection. So if you open Enterprise Manager - that is one connection, and if you then open Query Analyzer - that is a second connection. Even if you use the Tools menu to open Query Analyzer from Enterprise Manager, that is still TWO connections.

-SQLBill

Posting advice: FAQ481-4875

 

[LonnieJohnson]

thanks

if I use the
Alter database clientDb set single_user with Rollback immediate command do I have to turn off the set single_user with rollback when I reattach?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!