KILL command syntax - 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 2000 Forums

Transact-SQL (2000)

KILL command syntax

Author

Topic

John Newman
Starting Member

2 Posts

Posted - 2002-09-09 : 16:30:13

I need to automate a Kill statement to kill connections that are
left open overnight. Other processes need to run (restore db)
that will not run if connections are open to the db. I have written code that will supply the necessary spid's. However, the KILL command
does not seem to accept a variable as an argument.

The following code would work if the KILL statement would accept a
variable. (The table #tb2_sysprocesses contains the spids that are
connected that need to be killed.)

BEGIN TRANSACTION
Declare spid_cursor1 cursor
FOR
SELECT cast(spid as smallint)
from #tb2_sysprocesses
where spid is not null

OPEN SPID_cursor1

Declare @tempspid smallint

FETCH NEXT FROM spid_cursor1 INTO @tempspid
WHILE(@@FETCH_STATUS = 0)
BEGIN
Kill @tempspid

FETCH NEXT FROM spid_cursor1 INTO @tempspid
END
CLOSE spid_cursor1
DEALLOCATE spid_cursor1
COMMIT TRANSACTION

Can you please tell me a way to resolve this problem regarding restrictions on the KILL command syntax?

John
john.newman@morcinc.org

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-09 : 16:35:06

Dynamic SQL:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

You can build a dynamic SQL string that does not require a cursor at all, but will still include all of the SPIDs to be killed. It will be a lot easier to do it that way than to use dynamic SQL with a cursor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources