Killling spid
The following is a stored procedure that I created to kill idle processes.
However I get the following error on the kill statement: Line13: Incorrect
syntax near '@spid'...Could someone tell me the correct syntax...
CREATE PROCEDURE udpKILLProc
AS
DECLARE @spid smallint
DECLARE spid_cursor CURSOR FOR
SELECT spid
FROM sysprocesses
WHERE (datediff(mi,last_batch,GETDATE()) > 10
and loginame = "slj")
OPEN spid_cursor
FETCH NEXT FROM spid_cursor into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
KILL @spid
FETCH NEXT FROM spid_cursor into @spid
END
CLOSE spid_cursor
DEALLOCATE spid_cursor
# 1 Re: Killling spid
must not take a variable try - EXEC ( "KILL " + cast(@spid as varchar ))
instead of KILL @spid
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
"Leon Parker" <lparker@lortobco.com> wrote in message
news:3d94607d$1@10.1.10.29...
>
> The following is a stored procedure that I created to kill idle processes.
> However I get the following error on the kill statement: Line13:
Incorrect
> syntax near '@spid'...Could someone tell me the correct syntax...
>
>
> CREATE PROCEDURE udpKILLProc
> AS
> DECLARE @spid smallint
> DECLARE spid_cursor CURSOR FOR
> SELECT spid
> FROM sysprocesses
> WHERE (datediff(mi,last_batch,GETDATE()) > 10
> and loginame = "slj")
> OPEN spid_cursor
> FETCH NEXT FROM spid_cursor into @spid
> WHILE @@FETCH_STATUS = 0
> BEGIN
> KILL @spid
> FETCH NEXT FROM spid_cursor into @spid
> END
> CLOSE spid_cursor
> DEALLOCATE spid_cursor
