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 |
|
skond
Yak Posting Veteran
55 Posts |
 Posted - 2002-08-26 : 13:21:45
|
| Is there a way to find whether a stored procedure is currently running. I want to write something in the beginning of a proc to check whether it's already running and in that case just exit or else proceed with the proc. |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-08-26 : 14:09:38
|
| You could put the procedure within a job. There are commands to evaluate the status of a job. |
 |
|
|
Shaner
Starting Member
9 Posts |
Posted - 2002-08-26 : 14:21:02
|
| Here is one way to do it. Its a little funky but Ive used it before trying to do the same thing you are doing. Declare a global temporary table at the start of the proc and and drop the table at the end of the proc. At the start of the proc check for the tables existance...if it exists exit the proc.----------------------------------------------CREATE PROC spDoSomething ASIF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name = '##ExitDoSomething')BEGIN RETURN 1ENDELSEBEGIN CREATE TABLE ##ExitDoSomething(X bit) --Do stuff hereENDDROP TABLE ##ExitDoSomethingRETURN 0 |
|
|
|
skond
Yak Posting Veteran
55 Posts |
Posted - 2002-08-26 : 14:37:42
|
| Global temporary table could work. What happens if my proc fails and I try to re-run it. Will the global temp table still exist giving an impression that the proc is currently running. |
|
|
|
Shaner
Starting Member
9 Posts |
Posted - 2002-08-26 : 15:06:40
|
| Yes...that will certainly happen if there is no error handling. I did have to trap errors and wipe out the table if there was a failure. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-08-26 : 15:52:32
|
| You could also have the proc stuff its SPID and start time into the a global temp table. Then, when the proc starts it checks to see the last time it was started; if it was more than X seconds/minutes/hours, it figures the other one died and goes ahead and runs.Cheers-b |
|
|
|
|
|
|
Check for running proc