解决方法
作为夜间工作处理子系统的一部分,我们像下面的代码一样做,这比实际上更复杂;例如,我们正在处理多个相互依赖的作业集合,并从配置表中读取作业名称和超时值 – 但是它捕获了这个想法:
- 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