Calling a sql job synchronously

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clément Doss - 27 Mar 2008 19:56 GMT

Hi,

My customer has a complex system that is made of jobs, procedures and scripts that
should run almost every day.
The problem is that sometimes, some job can run "a little longer" and the other
procedures and scripts (and even jobs) should wait for jobs to end before starting.

Is there a way to call a sql job synchronously from T-SQL, from a dll or from some
language? Are there any sample code?

TIA,
Clément

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 20:03 GMT

No, jobs start asynchronously. You can have a bit in a table that gets
flipped to 1 when the job gets invoked (which only happens if the bit is
currently 0), and then flipped back upon successful completion.

Hi,

My customer has a complex system that is made of jobs, procedures and
scripts that
should run almost every day.
The problem is that sometimes, some job can run "a little longer" and the
other
procedures and scripts (and even jobs) should wait for jobs to end before
starting.

Is there a way to call a sql job synchronously from T-SQL, from a dll or
from some
language? Are there any sample code?

TIA,
Clément

Reply to this Message

Clément Doss - 27 Mar 2008 20:24 GMT

> No, jobs start asynchronously. You can have a bit in a table that gets
> flipped to 1 when the job gets invoked (which only happens if the bit is
> currently 0), and then flipped back upon successful completion.

I could just check the job status every second. Not very "elegant" solution, but
something like:

startjob("JobName");
while checkjob("JobName")== __running__ {
sleep( 1000 ); // in ms
}
return checkjob("JobName")== __success__;

For optimal perfomance, is there a DLL I can use, or should I use T-SQL?

TIA,
Clément

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 23:36 GMT

I could just check the job status every second.

Ugh. If the job takes 20 minutes to run, then that is a LOT of wasted
resources. Why not update a flag somewhere only when the job has completed
(with a job you can mark this whether or not the originating step
succeeded)?

Reply to this Message

TheSQLGuru - 27 Mar 2008 21:49 GMT

You could also simply have each job in succession start the next job that is
supposed to fire as it's last step.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> No, jobs start asynchronously. You can have a bit in a table that gets
> flipped to 1 when the job gets invoked (which only happens if the bit is
[quoted text clipped - 16 lines]
> TIA,
> Clément

Reply to this Message