Killing active connections before detaching a database

How can I kill all active connections to a database before detaching it?
I am calling sp_detach_db from an application but I keep getting the error
"Cannot detach because there are one or more active connections."
Thanks,
RonKill spid
Madhivanan|||2 ways
1
--loop through the sysprocesses
DECLARE @.sysDbName SYSNAME
SELECT @.sysDbName = 'northwind'
SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@.sysDbName)
DECLARE @.SPID SMALLINT
DECLARE @.SQL VARCHAR(255)
DECLARE @.MaxID INT, @.LoopID INT
SELECT @.LoopID =1,@.MaxID = MAX(ID) FROM #LoopProcess
WHILE @.LoopID <= @.MaxID
BEGIN
SELECT @.SPID = spid FROM #LoopProcess WHERE ID = @.LoopID
SELECT @.SQL = 'KILL ' + CONVERT(VARCHAR, @.SPID)
EXEC( @.SQL )
SELECT @.LoopID = @.LoopID +1
END
DROP TABLE #LoopProcess
2
--alter the DB by making it single user (all transaction will be rolled
back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do your restore here
-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER
http://sqlservercode.blogspot.com/|||That won't stop them from "crawling" back. ;)
Use:
use master
alter database <database name>
set single_user
with rollback immediate
...to kill all users immediately, or:
alter database <database name>
set single_user
with rollback after <number> seconds
...to give them time to finish their work.
ML
http://milambda.blogspot.com/|||That's what I call the "nuclear option". ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
> That won't stop them from "crawling" back. ;)
> Use:
> use master
> alter database <database name>
> set single_user
> with rollback immediate
> ...to kill all users immediately, or:
> alter database <database name>
> set single_user
> with rollback after <number> seconds
> ...to give them time to finish their work.
>
> ML
> --
> http://milambda.blogspot.com/|||THANKS!
No problem, the databases that will be called by this procedure are only
used under controlled circustances.
Ron
"JT" <someone@.microsoft.com> wrote in message
news:OkmBFzUOGHA.3100@.TK2MSFTNGP11.phx.gbl...
> That's what I call the "nuclear option". ;-)
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:15E4ACA2-392A-4986-B4A5-7E52EA9DDB33@.microsoft.com...
>|||When you gotta nuke'em, you gotta nuke'em. :)
ML
http://milambda.blogspot.com/