March 23, 2005 at 5:24 am
HI,
I want to use the KILL command inside a procedure.
I am getting the required spid in an int variable (@spid).
But its giving me error when I try to fire
exec (KILL @spid)
or EXECUTE sp_executesql ('kill ' + convert (varchar(10),@spid) ).
The error message is as follows.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'kill '.
Could anybody please help me understand where the problem lies.
March 23, 2005 at 6:10 am
Hi Somebody told me that you cannot calla stored procedure inside another. Is this true ?
If I wont be able to call KILL command from a stored procedure, can I do it from a script ? If yes, how ?
I tried calling the same from a script; but its not working with the above mentioned options.
March 23, 2005 at 8:02 am
The following works for me
DECLARE @id NVARCHAR(6)
DECLARE @stmt NVARCHAR(20)
SET @ID = 53
SET @stmt = 'KILL '+ @ID
EXEC sp_ExecuteSQL @stmt
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 23, 2005 at 8:05 am
You can call any amount of stored procs while you're in a stored proc. The only thing you have to watch for is the nesting level.
call sp1 : nesting level = 1
sp1 calls sp2 : nesting level = 2
sp2 calls sp1 : nesting level = 3 >> but you just started a recursinve algorithm. After you reach 32, you won't be able to call anything else that will increment the nesting level.
however if you have a sp like this
create proc spa
as
exec spb
exec spc
exec spd
exec spe
the max nesting level you will reach is 2 (assuming none of the 4 sps will call other sps).
Exec () cannot resolve the statement in the parenthesis, you must set it into a variable and use it like so :
Declare @ExecSQL as varchar(100)
SET @ExecSQL = 'KILL ' + CAST("Your spid goes here" As Varchar(10))
exec (@ExecSQL)
March 23, 2005 at 10:07 am
Thanks Frank & Remi.
Your suggestions helped me solve my problem.
I am still wondering why
exec ( 'kill ' + convert(varchar(10),@spid) ) is not working where as exec( @ExecSQ) works fine !!!
I find that the string value inside the paranthesis is the same in both the cases !!! Why do you think this is happening ?
Thanks once again for your help. 🙂
March 23, 2005 at 10:18 am
quoting myself :
Exec () cannot resolve the statement in the parenthesis, you must set it into a variable and use it like so ...
I don't know why it's like that but it's like that... Just one more gotcha.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply