That is what you can do. www.closeoutmerchant.com


CREATE TABLE #TmpWho
(
spid INT,
-- ecid INT,
status VARCHAR(150),
loginame VARCHAR(150),
hostname VARCHAR(150),
blk INT,
dbname VARCHAR(150),
cmd VARCHAR(150)
)
DECLARE @CNT int

/*DELETE ENTRIES FROM TEMP TABLE*/
DELETE #TmpWho

/*RUN PROC AND INSERT TO TEMP TABLE*/
INSERT INTO #TmpWho
EXEC sp_who
-- select * from #TmpWho

/*CHECK COUNT*/
SELECT @Cnt = COUNT(*) from #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))

While @Cnt<>0
BEGIN
DECLARE @spid INT
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR

SET @getspid = CURSOR FOR
SELECT spid
FROM #TmpWho
WHERE (dbname IN ('DB_1', 'DB_2'))
OPEN @getspid
FETCH NEXT FROM @getspid INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
-- PRINT(@tString)
EXEC(@tString)
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

/*CHECK IF SP_WHO GIVE YOU ANYTHING BACK*/
DELETE #TmpWho

/*RUN PROC AND INSERT TO TEMP TABLE*/
INSERT INTO #TmpWho
EXEC sp_who

/*CHECK COUNT*/
SELECT @Cnt = count(*) from #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))
END

SELECT * FROM #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))
/*DROP TEMP TABLE*/
DROP TABLE #TmpWho