dynamic killer

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.

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> 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.
> Gary

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