Calling a sql job synchronously

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 theother
procedures and scripts (and even jobs) should wait for jobs to end beforestarting.
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,
Clment
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.
"Clment Doss" <cdoss@.nospam.dhs.com.br> wrote in message
news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
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,
Clment
|||Aaron Bertrand [SQL Server MVP] wrote:
> 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,
Clment
|||You could also simply have each job in succession start the next job that is
supposed to fire as it's last step.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eh%23DT2DkIHA.2276@.TK2MSFTNGP05.phx.gbl...
> 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.
>
>
> "Clment Doss" <cdoss@.nospam.dhs.com.br> wrote in message
> news:%23iivexDkIHA.4396@.TK2MSFTNGP04.phx.gbl...
> 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,
> Clment
>
|||>>
I could just check the job status every second.[vbcol=seagreen]
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)?