asked
Check if SQL job is running or not
If I execute below statement to check SQL job running status, it gives me correct result. DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' **But if I add order by START_EXECUTION_DATE DESC clause it fails.** DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID ORDER BY START_EXECUTION_DATE DESC; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' why?. Logically it should work.
No sense re-inventing this wheel. @Pavel Pawlowski has published a working set of functions to do just what you are after -
http://www.pawlowski.cz/2011/04/querying-agent-job-status-executing-waiting-job-completion-t-sql/
My initial guess, without knowing what version of SQL Server or the exact error (if there is one) is that when setting the variable value in conjunction with an ORDER BY, you need to include a TOP 1. Like this: DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT TOP 1 @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID ORDER BY START_EXECUTION_DATE DESC; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' In testing, if I removed the TOP 1 it would just set the variable to a NULL value. Including the TOP 1 set the value correctly. I'm assuming that's the error, setting the variable to NULL. Hope that helps!
17 People are following this question.
