T-sql procedure to wait for sql server agent job
/*
Awaits job to finish, then returns job_status
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
null = Job never executed / no history
If job was executed and already finished successfully before this proc starts, it will return 1 (success)
*/
create procedure tools.AwaitSqlServerAgentJob
@jobName sysname
as
declare @jobid uniqueidentifier = (select job_id from msdb.dbo.sysjobs where name = @jobname )
declare @sessionId int = (select max(session_id) from msdb.dbo.sysjobactivity where job_id=@jobid )
declare @laststop datetime, @lastrunstatus int
--Wait loop
while 1=1
begin
set @laststop = (select stop_execution_date from msdb.dbo.sysjobactivity where session_id=@sessionId and job_id=@jobid)
if @laststop is not null
break
waitfor delay '00:00:10'
end
--After wait loop: check for status of last run
select top 1 @lastrunstatus=run_status from msdb.dbo.sysjobhistory where step_id = 0 and job_id=@jobid order by instance_id desc
return @lastrunstatus
Awaits job to finish, then returns job_status
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
null = Job never executed / no history
If job was executed and already finished successfully before this proc starts, it will return 1 (success)
*/
create procedure tools.AwaitSqlServerAgentJob
@jobName sysname
as
declare @jobid uniqueidentifier = (select job_id from msdb.dbo.sysjobs where name = @jobname )
declare @sessionId int = (select max(session_id) from msdb.dbo.sysjobactivity where job_id=@jobid )
declare @laststop datetime, @lastrunstatus int
--Wait loop
while 1=1
begin
set @laststop = (select stop_execution_date from msdb.dbo.sysjobactivity where session_id=@sessionId and job_id=@jobid)
if @laststop is not null
break
waitfor delay '00:00:10'
end
--After wait loop: check for status of last run
select top 1 @lastrunstatus=run_status from msdb.dbo.sysjobhistory where step_id = 0 and job_id=@jobid order by instance_id desc
return @lastrunstatus
Comments