| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2004-01-28 : 13:39:58
|
| I get time-outs on the search function.If have a job that runs every 2 minutes. If there's a new record where processed is null a stored procedure is run. (DTS jobs and bla bla, I had to do it to work around permissions etc.) BUT, I don't want two of the same stored procedures run at the same time (there are more jobs who can start the stored procedure). How can I check if a stored procedure is running? Or do I have to make a check in check out system?? Like: when the procedure is running create a record with a start moment. When is finishes edit the record and put in a end moment. If there's records with start but no end, the procedure is running...Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-28 : 13:43:27
|
| To see if an object is currently being used, you would have to check the system tables. But this is highly not recommended. So a check in check out system like you mentioned would be better. But what happens if the check in occurs, but the process fails before it had time to check out?Tara |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-28 : 14:43:41
|
| Read up on application locks in BOL, they are the T-SQL equivalent of semaphores in application languages and can give you the functionality you're after.JonathanGaming will never be the same |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-01-29 : 04:01:22
|
| I used the keywords in BOL but can't find anything about it. Could you be more specific?Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-01-29 : 04:10:17
|
| PS: Is it possible then to check if a specific job is running?Or how do I use msdb.dbo.sp_help_job to put the execution status into a variable?Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-29 : 06:37:41
|
| You can wrap this into a sp...declare @ob int, @st intexec sp_OACreate 'SQLDMO.SQLServer', @ob outputexec sp_OAMethod @ob, 'Connect("myServer", "sa", "pwd")'exec sp_OAGetProperty @ob,'JobServer.Jobs("myJobName").CurrentRunStatus', @st outputexec sp_OADestroy @obselect @st -- value 1 means the job is still executing...-- 1 Job is executing.-- 4 Job is idle, awaiting its next scheduled execution.-- 7 All executable job steps have completed. Job history---- logging is being performed. |
|
|
|
richardhayes
Starting Member
1 Post |
Posted - 2006-04-21 : 11:47:47
|
| You are missing a crucial line! Before you destroy the object you must disconnect using :EXEC sp_OAMethod @ob , 'Disconnect' If you don't do this, you end up with lots of SQL-DMO processes left sleeping indefinitely, which will eventually bring the server to a standstill. |
|
|
|
Krankensteins
Starting Member
24 Posts |
Posted - 2006-04-21 : 16:49:35
|
| Just create som place (new table or colon in exsisting table) and in procedure who starts job create validation wth one block ( begin transaction -->> commit) who validates value and if alredy run value = true then didn't start yob. |
|
|
|
|
Check if a stored procedure is running