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.