IsSQLAgentJobRunning.proc.sql 1.58 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
/*
	this needs to be deployed in SSISDB on the appservers

*/

CREATE PROCEDURE IsSQLAgentJobRunning (
	@JobName varchar(500) ,
	@Statement varchar(2000) OUTPUT
)
AS 
BEGIN
	SET NOCOUNT ON;
	SELECT @Statement = 'Not Running';

	CREATE TABLE #current_job_status   
	(   
		[JobID]               UNIQUEIDENTIFIER,   
		[LastRunDate]         NVARCHAR(50),   
		[LastRunTime]         NVARCHAR(50),   
		[NextRunDate]         NVARCHAR(50),   
		[NextRunTime]         NVARCHAR(50),   
		[NextRunScheduleID]   NVARCHAR(50),   
		[RequestedToRun]      NVARCHAR(50),   
		[RequestSource]       NVARCHAR(50),   
		[RequestSourceID]     NVARCHAR(50),   
		[Running]             NVARCHAR(50),   
		[CurrentStep]         NVARCHAR(50),   
		[CurrentRetryAttempt] NVARCHAR(50),   
		[State]               INT   
	)   


	INSERT INTO #current_job_status   
	EXEC [master].sys.xp_sqlagent_enum_jobs 1,''

	DECLARE @Output TABLE (Output varchar(4000));
	INSERT INTO @Output
	SELECT 
		@@servername + ' : ' + j.name + ' : was running when JobManager launched the next processing window at : ' + convert(varchar(200),getdate())		
	FROM #current_job_status c
	JOIN msdb.dbo.sysjobs j 
		ON j.job_id = c.JobID
	WHERE c.State = 1
	AND j.name = @JobName

	IF EXISTS (select * from @Output) 
	BEGIN
		SELECT @Statement = Output FROM @Output
	END;
END;
GO

--DECLARE @Something varchar(2000);
--EXEC IsJobRunning @JobName = 'Rovi Video 1.1 UB_TR', @Statement = @Something OUTPUT;
--SELECT @Something

--EXEC IsJobRunning @JobName = '', @Statement = @Something OUTPUT;
--SELECT @Something