I am currently building an SQL Server job. One of the steps of the job is to run sp_start_job to call another job that already exists on the server. However, I’m finding that the server immediately goes on to the next step right after calling the job. How can I get the job to wait for the step to complete before going on to the next job?
Zev Steinhardt
Is it possible to include the step(s) of the other job as steps in the new job? I realise this will probably involve some duplication of code but it would be the simplest way to guarantee they run in order.
Otherwise I think your best bet would be to call sp_start_job, then construct a WHILE loop that calls sp_help_job to get the @execution_status output parameter. You can use WAITFOR DELAY inside the loop to stop things hanging up. Stick all this at the top of the SQL called in the next step and it should wait for the previous step to complete.
I haven’t tested this, so you might need to experiment a bit. Since the other job will start asynchronously on another thread you may also have to construct two WHILE loops: one to wait for the job to start, and then the one to wait for it to finish.
This is definitely not ideal though. Feel free to email me if I can be of more help.
Armilla,
Thanks for the help.
The sub-job being called is rather long and complicated and changes somewhat frequently. To have to main two sets of code will probably not work.
The WHILE loop idea is very interesting - I didn’t think of trying something like that. It’s certainly not ideal, but it is a possibility.
Of course, if you have any other ideas, I’m open to them.
Zev Steinhardt