Killing a process through a stroed proc

Question

Post reply

Killing a process through a stroed proc

Charlottecb

Hall of Fame

Points: 3932
More actions
March 3, 2004 at 5:37 am

#178699

Hi All,
I am trying to kill a process with a stored proc. The spID to be killed is passed into the StoredProc. I cannot get it to compile unless I remove the parameter and hardcode it (which is not what I want).
What am I doing wrong ?
Thanks
CCB
CREATE PROCEDURE [dbo].[prc004KillProcess]
(@intSPID smallint)
AS
Kill @intSPID
GO

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

womalley Ten Centuries Points: 1329 More actions · Answer 1

did you try using sp_sqlexec ??

Try this

declare @r SMALLINT

,@sql VARCHAR(200)

SET @r = 60

SET @sql = 'kill ' + CAST(@r AS VARCHAR(5))

exec sp_sqlexec @sql

hope this helps

William O'Malley

noeld SSC Guru Points: 96590 More actions · Answer 2

CREATE PROCEDURE [dbo].[prc004KillProcess]

(@intSPID smallint)

AS

DECLARE @STR as vaschar(100)

SET @STR ='KILL ' + cast (@intSPID as varchar(3))

exec (@str)

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

sp_sqlexec() ???

I think you mean sp_executesql()

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 4

I guess that will not work....finally when it gets to executing the Kill statement it will throw an error saying the Kill "expects a handle (SPID) tinyint."

Correct me if i am wrong!

Cheers!

Arvind

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

This works fine, well you have to make it a varchar not a vaschar, but that's just semantics.

Shamless self promotion - read my blog http://sirsql.net

noeld SSC Guru Points: 96590 More actions · Answer 6

that was a typo. I was just trying to help. BTW is not semantics is syntaxis

* Noel

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

I have some cream that will take care of that

Shamless self promotion - read my blog http://sirsql.net

womalley Ten Centuries Points: 1329 More actions · Answer 8

nope.. sorry

I ment sp_sqlexec

Look in the Master database under stored procedures.. you will find sp_sqlexec

there is how ever a stored procedure sp_executesql

The difference?? Well sp_executesql is an extended stored procedure

sp_sqlexec is not...

-- Procedure Script

create procedure sp_sqlexec --- 1996/04/08 00:00

@p1 text

as

exec(@p1)

--------------------

Well you can use either one.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Well, you shouldn't use sp_sqlexec any more as BOL states for SQL Server 2000

>Removed; no longer available. Remove all references to sp_sqlexec.

It can be found when searching for sq_sqlexec

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dhafir Moussa Newbie Points: 1 More actions · Answer 10

Very interesting topic!

The example above works fine if you have the SPID number.

There is a case when you need to kill an orphaned distributed transaction (SPID = -2) where in this case the KILL command will need the UOW instead of the process ID, i.e

KILL '9BE76755-54C9-4697-87BB-CD2D1F5648EC'

where 9BE76755-54C9-4697-87BB-CD2D1F5648EC is the UOW.

I trouble is that the KILL command would want the UOW to be between two apostrophes ('),

I tried this:

SET @command ='KILL ' + cast (@uow as varchar(50))

but it didn't work!

Any idea?

Dhafir

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 11

Try

SET @command ='KILL ''' + cast (@uow as varchar(50)) + ''''

Shamless self promotion - read my blog http://sirsql.net