SQL Agent that runs SQL agents doesn't wait between steps

Question

I have a SQL agent job that I want to have kicking off 4 other SQL agent jobs using the following:

EXEC msdb.dbo.sp_start_job 'My Other Job'

Now each job is its own step, however, the job does not wait to see if the job finishes successfully, it just kicks off the next step, so all 4 are running at once. As there a way to make it wait before it kicks off the next job?

Thanks

Answer 1

I've waited before with this:

WAITFOR DELAY '00:00:02';
while exists (select * from msdb.dbo.sysjobs j 
                inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id 
                where name = 'My Other Job' 
                and stop_execution_date is null and start_execution_date is not null)
begin
    WAITFOR DELAY '00:00:02';   
end

where you can pause for whatever makes sense. My job was relatively small, so I only waited 2 seconds between checking.

Answer 2

A SQL Agent job step waits it receives a "success" message before going to the next step. However, when that step is to call msdb.dbo.sp_start_job, it receives a "success" message as soon as the target job is started. The message is saying "Yes, I successfully started the job".

There is no way to make it wait for completion of that job, if this is the method you use.

However, if you consolidate your five jobs into one job, and put the steps in the order that you want, each step will wait until the previous step is completed before starting.