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 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 StackOverflow first, because I was focusing on improvement of the code. Still valid. But my guess is that people here will in general have smarter things to say about why I should not try to do this the way I'm doing it now, or provide good alternatives.)

EDIT (July 25)
Apparently there is not too much wrong with my script, according to the low number of answers pointing out problems with it :-) The alternative to this kind of scripting seems to be to use a tool that is designed for these tasks (like SQL Sentry Event Manager or ...) - or to write such a tool yourself. We won't be buying such a tool at my current company, so for now I'll just stick with the script.

Answer 1

Disclaimer: I work for SQL Sentry.

Our SQL Sentry Event Manager product has a facility built exactly for this: to chain jobs and arrange them in various workflow orders.

I started using SQL Sentry years ago, before I ever joined the company, to do exactly this. What I wanted was a way to start a restore job on our test server immediately after the backup on production had finished.

What I had originally implemented was just a substantial buffer between the backup job start time and the restore start time. This wasn't exactly foolproof; since backup times varied the buffer often left us with wasted time where a restore hadn't started even though it could have. And occasionally the buffer wasn't enough.

What I implemented next was similar to what you have - I wrote a job on the test server that started shortly after the scheduled backup, and kept polling to see when the job was finished. That was later amended to just have a second step in the backup job that updated a table on the test server. Not really much different, except the restore job only had to watch a table locally instead of monitoring the job history remotely. Thinking back this could have been a trigger on that table that called sp_start_job so the job didn't have to run every n minutes (or be scheduled at all).

The final solution was to chain jobs together ... when the backup on server A finishes, Event Manager starts the restore job on server B. And if there was a third job, and a fourth job, or conditional logic based on what to do when a job fails vs. succeeds, etc., this can all be accounted for. The workflow designer will remind you quite a bit of SSIS:

The underlying mechanics of what I'm describing is not rocket surgery, of course. You could write this type of chaining wrapper yourself if you sat down and did it. Just providing you one alternative where you don't have to.

Answer 2

The main problem with your approach is that you have to continually loop round until something happens (which could be an awfully long time or even never) and that doesn't quite feel right. Which is why I guess you're asking the question.

So, how about using a data-driven approach to your problem? For example, create an 'audit' table that each job writes to as it starts and finishes:

Job Name | Start Time | End Time

---------+-------------------+------------------

Test1 2012-07-26 07:30 2012-07-26 07:35

Create a 'processing' table that lists all the jobs and the order that they need to be executed in:

Job Name | Run Order

---------+---------

Test1 | 1

Test2 | 2

Test3 | 3

Create an insert trigger on the audit table, so that when a job completes and the audit record is inserted, the trigger queries the processing table for the next job (by Run Order) and then launches it.

The benefits to this approach are:

1. It is fairly simple to develop and maintain.
2. It gives the ability to add new jobs or change the order of existing jobs via the processing table without having to change a line of code.
3. The audit table gives some visibility as to when things have happened.
4. It doesn't waste CPU cycles. The trigger will only fire when something has happened.
5. It feels right ;-)

HTH