Can I cancel an sp via code?

[jsteph]

Hi all,
I have a VB program that runs an async. stored procedure. The user can watch the progress by reading a small log table that is written to by the sp.

I would like to give the user the capability to cancel just his instance of that procedure, but not kill his entire session. How would I go about that?
Thanks,
--Jim

 

[jbenson001]

You would have to find a way to get the SPID of that SP and use the KILL command. However, this is not reccomended since you need sa rights to do that. I am not sure if there is another way to cancel a SP once it is run.

 

[SQLDenis]

you need to grab the processid by using @@SPID, open another connection and execute KILL + that ID

however I would be very carefull doing this because if the process is done before you execute the KILL command you might be killing someone else process

this of course assumes that you would have KILL privileges (which none of my users have)

run this to see how this works

declare @i int
select @i =@@SPID

select @i
exec ('kill ' +  @i)



Server: Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

you will need to open another window to kill that @@SPID

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jsteph]

Denis,

this of course assumes that you would have KILL privileges (which none of my users have)

I guess that's part of the issue--I don't want the user to have those privileges, but is there a way to delegate that via another sp. For instance, I'd like to create a separate sp that recieves a spid as a parameter and kills that spid.

The users would have rights to run this procedure, but the procedure would be done like a 'run as' or 'with owner access' where the procedure has the necessary permissions but the user running it doesn't--he can only run the procedure, but can't alter it or anything.
Thanks,
--Jim

 

[JackVam]

You will need to do this asychronously and in a separate thread

All the IT jobs in one place -

http://www.ITjobfeed.com