这个问题在这里已经有了答案:
8年前关闭。
使用 sp_start_job 启动后，有没有办法确定 sql 代理作业何时完成？
最佳答案
这个 article 描述了一个 SP 来启动一个 sql 代理作业并等待。
-- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
declare @xp_results TABLE ( job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- start the job
declare @r as int
exec @r = msdb..sp_start_job @job
-- quit if unable to start
if @r<>0
RAISERROR (N'Could not start job: %s.', 16, 2, @job)
-- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
WAITFOR DELAY '0:0:01';
set @seccount = 1
-- check job run state
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
while @running<>0
begin
WAITFOR DELAY '0:0:01';
set @seccount = @seccount + 1
delete from @xp_results
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
set @running= (SELECT top 1 running from @xp_results)
end
-- result: not ok (=1) if still running
if @running <> 0 begin
-- still running
return 0
end
else begin
-- did it finish ok ?
set @run_status = 0
select @run_status=run_status
from msdb.dbo.sysjobhistory
where job_id=@job_id
and cast(run_date as bigint) * 1000000 + run_time >= @start_job
if @run_status=1
return 1 --finished ok
else --error
RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)
end
END TRY