I have an interesting situation:

I have two DTS packages that affect the same table so if they run at the
same time, we get deadlocks all over.
I'd like to schedule each to check if the other is running an re-try 3 times
after 1/2 an hour. So, add a new step in the schedule to do this and fail if
the other is running, right?

But how do you determine if another DTS package is currently running in the
system tables? The msdb.dbo.sysjobHistory.run_status column never gives a
status of "4". So where do I look?

Thanks,

- Bill Connelly

In article <#sFdYk3YDHA.1492 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Bill Connelly
<bill.connelly (AT) crossmark (DOT) com> writes
The only reliable way to see if another job is running or not is for
that job to explicitly set a flag to say that it is running. I do this
as part of a processes to detect long running jobs. You could add a
start step to the jobs that inserted something into a table to say I
starting, and then when complete remove it, or have another regular job
that checked the status. Completed is reliable enough. I forget my exact
method, but I know starting had to be logged manually.

An alternative to a table would be to use sp_getapplock and
sp_releaseapplock to indicate the current execution of a job.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

msdb.sysdtssteplog is a good place to start


--
Posted via http://dbforums.com