解决方法
作为夜间工作处理子系统的一部分,我们像下面的代码一样做,这比实际上更复杂;例如,我们正在处理多个相互依赖的作业集合,并从配置表中读取作业名称和超时值 – 但是它捕获了这个想法：
DECLARE @JobToRun NVARCHAR(128) = 'My Agent Job'
DECLARE @dtStart DATETIME = GETDATE(),@dtCurr DATETIME
DECLARE @ExecutionStatus INT,@LastRunOutcome INT,@MaxTimeExceeded BIT = 0
DECLARE @TimeoutMinutes INT = 180
EXEC msdb.dbo.sp_start_job @JobToRun
SET @dtCurr = GETDATE()
WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:10'
SELECT @ExecutionStatus=current_execution_status,@LastRunOutcome=last_run_outcome
FROM OPENQUERY(LocalServer,'set fmtonly off; exec msdb.dbo.sp_help_job') where [name] = @JobToRun
IF @ExecutionStatus <> 4
BEGIN -- job is running or finishing (not idle)
SET @dtCurr=GETDATE()
IF DATEDIFF(mi,@dtStart,@dtCurr) > @TimeoutMinutes
BEGIN
EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun
-- could log info,raise error,send email etc here
END
ELSE
BEGIN
CONTINUE
END
END
IF @LastRunOutcome = 1 -- the job just finished with success flag
BEGIN
-- job succeeded,do whatever is needed here
print 'job succeeded'
END
END
