SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andre Pageot
-- Create date: 29 july 09
-- Description:	Check for concurrent running jobs
-- returns 1 if jobs are running 0 if not
-- this procedure resides in the master DB to avoid a collation conflicts
-- USEAGE:-
-- ammend the job names for the jobs you consider are no allowed to run concurrently
-- when writing job step 1 of job name 'Job 1' you must place this at the begining of step, this is only required once for each job
--	wait for a window to open before we begin
--		WHILE (2 > 1) 
--			BEGIN 
--				DECLARE @Running bit
--				EXEC master.dbo.usp_LCEDependantJobRunning @CurrentExecutingJobName = 
--				'Job 1'	
--				,@Result = @Running output
--
--				IF @Running = 0 BREAK
--				--hold up a second
--				WAITFOR DELAY '00:00:01'
--			END
-- =============================================
ALTER PROCEDURE [dbo].[usp_LCEDependantJobRunning]
	@Result bit output,
	@CurrentExecutingJobName nvarchar(500)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @JobNames table([name] nvarchar(500))
	DECLARE @JobIDs Table(Job_ID uniqueidentifier)
	DECLARE @name SYSNAME SELECT @name = SUSER_SNAME() 

	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)
	--job_state
	--Value Description
	--1  Executing.
	--2  Waiting for thread.
	--3  Between retries.
	--4  Idle.
	--5  Suspended.
	--7  Performing completion actions.

	INSERT INTO @JobNames ([name]) values ('job name 1')
	INSERT INTO @JobNames ([name]) values ('job name 2')
	INSERT INTO @JobNames ([name]) values ('job name 3')
	INSERT INTO @JobNames ([name]) values ('job name 4')

	INSERT INTO @JobIDs
	SELECT [job_id] From msdb.dbo.sysjobs WHERE 
	[name] in (select [name] from @JobNames)
	AND [name] <> @CurrentExecutingJobName

	INSERT INTO @xp_results exec master.dbo.xp_sqlagent_enum_jobs 1 , @name 

	IF (SELECT count(*) FROM @JobIDs) > 0
		BEGIN
	--		SELECT 
	--		(SELECT [Name] From msdb.dbo.sysjobs WHERE [Job_ID] = R.Job_ID) AS [Name],
	--		Job_ID,
	--		Job_state
	--		FROM @xp_results R WHERE Job_State <> 4 AND Job_ID = @JobID

			IF (SELECT count(Job_ID) FROM @xp_results WHERE Job_State <> 4 AND Job_ID in (SELECT job_id FROM @JobIDs)) > 0
				BEGIN
					SET @Result = 1
				END
			ELSE
				BEGIN
					SET @Result = 0
				END
		END
	ELSE
		BEGIN
	--		SELECT 
	--		(SELECT [Name] From msdb.dbo.sysjobs WHERE [Job_ID] = R.Job_ID) AS [Name],
	--		Job_ID,
	--		Job_state
	--		FROM @xp_results R WHERE Job_State <> 4 
			IF (SELECT Count(Job_ID) FROM @xp_results WHERE Job_State <> 4) > 0 
				BEGIN
					SET @Result = 1
				END
			ELSE
				BEGIN
					SET @Result = 0
				END
		END

	-- Return the result of the function
	RETURN @Result
END
GO