Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-03-28 : 19:26:40
|
| I've got a stored procedure that runs on a schedule, but which can also be triggered by an application event (the update needs to happen immediately).I'm seeing a cosmetic but annoying error when the application event happens just after the scheduled task starts; the app gets an ugly deadlock error.Is there any easy way to put code at the beginning of the procedure that will check if another copy is already running, and bail out if so?Thanks-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-28 : 19:41:13
|
| Here's a neat trick I got from Ken Henderson'sThe Guru's Guide to SQL Server Stored Procedures, XML, and HTMLCREATE PROCEDURE myProc ASSET NOCOUNT ONIF EXISTS(SELECT * FROM master..sysprocesses WHERE context_info = 0x123456)BEGIN RAISERROR('Process already started, aborting.', 16, 1) RETURNENDSET CONTEXT_INFO 0x123456...rest of procedure codeSET CONTEXT_INFO 0x0Make the binary context info value unique for each stored procedure you want to manage in this way. The already-running process will complete and reset the context info back to zero, so you do not have to do any extra work. If the connection drops for some reason, the context info will disappear along with the spid itself. It's possible that an error may leave the connection open with that context info intact, I've not tested this scenario though. |
 |
|
|
|
|
|
Don't run SP if already running?