------------------------------------------------------
--John Huang's Blog
--http://www.sqlnotes.info/tag/xp_sqlagent_enum_jobs/
------------------------------------------------------
(
JobIDuniqueidentifier NOTNULL, -- Job ID
LastRunDateint, LastRunTime int, -- Last run date and time
NextRunDateint, NextRunTime int, -- Next run date and time
NextRunScheduleIDint, -- an internal schedule id
RequestedToRunint, RequestSource int, RequestSourceID varchar(128),
Runningint, -- 0 or 1, 1 means the job is executing
CurrentStepint, -- which step is running
CurrentRetryAttemptint, -- retry attempt
JobStateint ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
)
DECLARE @job_ownersysname SET @job_owner= SUSER_SNAME()
DECLARE @CurrentDateTimeDATETIME
DECLARE @LastRunOutcomeINT
DECLARE @MaxTimeExceededBIT = 0
SET @CurrentDateTime= GETDATE()
WHILE 1=1 AND @IsJobRunning=1
BEGIN
WAITFORDELAY '00:01:00' – how often to check job status, every 1 min
INSERTINTO @ExecutionStatusTable
EXECUTEmaster.dbo.xp_sqlagent_enum_jobs 1, @job_owner
SELECT@IsJobRunning =x.Running
FROM@ExecutionStatusTable x
INNERJOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID
WHEREsj.name =@JobToRun --your job's name
BEGIN-- job is running or finishing (not idle)
SET @CurrentDateTime=GETDATE()
IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes
BEGIN
-------------------------------------------
--MSDN sp_stop_job (Transact-SQL)
--http://msdn.microsoft.com/en-us/library/ms182793.aspx
-------------------------------------------
EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun
-- job stoped, do whatever is needed here
END
ELSE
BEGIN
print 'running...' +CONVERT(VARCHAR(100),DATEDIFF(mi, @StartDateTime, @CurrentDateTime))
CONTINUE
END
END
IF@IsJobRunning =0
BEGIN
-- job not running, do whatever is needed here
print 'job not running'
END
END
