4

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 curious about the following things, hope you can provide me with some insights:

  • What are the problems with this approach?
  • Can you suggest a better way to do this?

(I posted this question at dba.stackexchange.com as well, to profit from the less-programming-more-dba'ing point of view too.)

Community
  • 1
  • 1
Josien
  • 13,079
  • 5
  • 36
  • 53
  • Instead of having one main job call all the others, why not chain them together? Have job 1 call job 2 when finished; job 2 calls job 3; etc. Avoids that nasty while loop. – Esoteric Screen Name Jul 13 '12 at 19:17
  • 1
    @EsotericScreenName: that was the earlier setup. It seemed an unclear/cluttered solution, because the job chain is not obvious from inspecting just one job - you have to check out all jobs involved, one after another. – Josien Jul 13 '12 at 19:34

2 Answers2

3

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • +1 for "still gonna be icky". It's very icky; the approach in the second paragraph is much better. – Jeff Siver Jul 13 '12 at 21:44
  • I Agree that it feels icky, but I'm still wondering why (hard scientific/experience facts) that's so? – Josien Jul 13 '12 at 22:29
  • 1
    For me, the ick factor stems from polling. Seems such an inefficient waste of resources. Second stems from restart-ability. Using something like the second approach would allow you to define job dependencies as well as track current job process. – billinkc Jul 14 '12 at 00:35
  • 1
    A follow-on question was posted over on [dba.stackexchange](http://dba.stackexchange.com/questions/20891/good-way-to-call-multiple-sql-server-agent-jobs-sequentially-from-one-main-job). There I described how [SQL Sentry Event Manager](http://sqlsentry.net/event-manager/sql-server-enterprise.asp) can be used to solve this problem, with the disclaimer obviously that I work for SQL Sentry. – Aaron Bertrand Jul 14 '12 at 16:19
  • Thanks! I'm gonna try your second suggestion and see how that works out. – Josien Jul 27 '12 at 13:16
0

No problem with the approach. I was doing somewhat like your requirement only and i used sysjobhistory table from msdb to see the run status because of some other reasons.

Coming back to your question, Please refer msdb.dbo.sp_start_job stored procedure using the same approach and its been used by one default Microsoft BizTalk job 'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb' to call another dependent default biztalk job 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb'. Even there is one stored procedure in BizTalk messagebox to check the status of job. Please refer 'int_IsAgentJobRunning' in BizTalk messagebox.

Deepak
  • 1