从一个主要作业顺序调用多个SQL Server代理作业的好方法？

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.

我有几个应该按顺序运行的SQL Server代理作业。为了更好地概述应该执行的作业，我创建了一个主要作业，通过调用EXEC msdb.dbo.sp_start_job N'TEST1'来调用其他作业。 sp_start_job立即完成（作业步骤1），但后来我希望我的主作业等到作业TEST1完成后再调用下一个作业。

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:

所以我写了这个小脚本，在调用作业后立即开始执行（作业步骤2），并强制主作业等到子作业完成：

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.

这很好用。但我觉得更聪明和/或更安全（WHILE 1 = 1？）的解决方案应该是可行的。

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.)

（我也在dba.stackexchange.com上发布了这个问题，从更少编程的dba'ing观点中获益。）

2 个解决方案

#1

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.

如果您选择轮询一个表，那么您需要查看msdb.dbo.sysjobhistory并等到run_status不是4.但仍然会icky。

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.

第三种更强大的方法是使用Service Broker之类的东西来处理进程之间的通信和信令。这需要更多的设置，但它是进程之间进行通信的最多机制。

#2

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.

方法没问题。我有点像你的要求，我使用msdb的sysjobhistory表来查看运行状态，因为其他一些原因。

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.

回到你的问题，请使用相同的方法引用msdb.dbo.sp_start_job存储过程，并且它被一个默认的Microsoft BizTalk作业'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb'用来调用另一个依赖的默认biztalk作业'MessageBox_Message_Cleanup_BizTalkMsgBoxDb'。即使BizTalk消息框中有一个存储过程来检查作业的状态。请参阅BizTalk消息框中的'int_IsAgentJobRunning'。

#1