-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|