Kill execution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Kill execution

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 21:41:33

[code]CREATE PROCEDURE [dbo].[KILL_ALL_PROCESSES]
@DBName VARCHAR(20)
AS
BEGIN
DECLARE @SPid INT
DECLARE MY_CURSOR CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DBName) AND SPId <> @@SPId
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('KILL ' + @SPId)
FETCH NEXT FROM MY_CURSOR INTO @SPId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
END[/code]
Hi, how to remove the cursor away?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 22:41:38

you can't. Not when you need to exec() for each SPID.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 22:47:16

>"< i thought all must convert to set-based...haha

Hope can help...but advise to wait pros with confirmation...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-11 : 00:33:08

One execute statement and no cursor:


declare @t table (spid int not null)

insert into @t
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DBName) AND SPId <> @@SPId

declare @sql nvarchar(max)
set @sql = ''

select
@sql = @sql+
'
kill '+convert(varchar(20),Spid)+';
'
FROM @t

exec (@sql)

CODO ERGO SUM

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-11 : 01:38:54

MVJ today so free...finally once you reply my post ahaha

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources