Good way to call multiple SQL Server Agent jobs sequentially from one main job?

Question

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to `EXEC msdb.dbo.sp_start_job N'TEST1'`. The `sp_start_job` finishes instantly (Job Step 1), but then I want my main job to wait until job `TEST1` has finished before calling the next job. So I have written this small script that starts executing right after the job is called (Job Step 2), and forces the main job to wait until the sub job has finished: ``` WHILE 1 = 1 BEGIN WAITFOR DELAY '00:05:00.000'; SELECT * INTO #jobs FROM OPENROWSET('SQLNCLI', 'Server=TESTSERVER;Trusted_Connection=yes;', 'EXEC msdb.dbo.sp_help_job @job_name = N''TEST1'', @execution_status = 0, @job_aspect = N''JOB'''); IF NOT (EXISTS (SELECT top 1 * FROM #jobs)) BEGIN BREAK END; DROP TABLE #jobs; END; ``` This works well enough. But I got the feeling smarter and/or safer (`WHILE 1 = 1`?) solutions should be possible. I'm curi…

Accepted Answer

If you choose to poll a table, then you'd need to look at msdb.dbo.sysjobhistory and wait until the run_status is not 4. Still gonna be icky though.

Perhaps a different approach would be for the last step of the jobs, fail or success, to make an entry back on the "master" job server that the process has completed and then you simply look locally. Might also make tracking down "what the heck happened" easier by consolidating starts and stops at a centralized job server.

A third and much more robust approach would be to use something like Service Broker to handle communicating and signaling between processes. That'll require much more setup but it'd be the most mechanism for communicating between processes.

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

Solution

Problem