dynamic killer -- sql server (alternate)

rooty_hill2002 NO[at]SPAM yahoo.com.au
10/7/2004 9:58:03 PM

Hi, guys!

I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:

declare @id int
begin
set @id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @id
end
GO

It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@id'."

Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?

Thanks in advance.

Simon Hayes
10/8/2004 1:33:13 PM

[quoted text, click to view]

Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:

exec('kill ' + convert(varchar, @id))

Simon

sql server (alternate) : dynamic killer