Wait for me!

I was given a situation at work a couple of days ago where we had a conflict between SSIS jobs.  There are two jobs in SQL Agent that get called by an external process.  They both insert to a single, specific table.  If they fire at the same time, they deadlock.  So how do we keep the two from stepping on each other?  That was my task, so I wrote the following proc in response.  You can insert it at the beginning of the job, using the other job name as the @job_name parameter.

CREATE PROCEDURE dbo.[WaitForJob]
/****************************************************************
NAME:
 WaitForJob

DESCRIPTION:
 This is used as a delay step. If you have two jobs that should
 not run at the same time, you can use this to check to see if
 the interfering job is running before continuing.  Like waiting
 for a backup to complete before running a reindex.

DEPENDENCIES:
 XP_SQLAGENT_ENUM_JOBS - this is an undocumented stored proc in
 the master database.  As such, this script is not necessarily
 going to work on your version of SQL.  It was written on SQL
 2005, though it may work on other versions.  Use at your own
 risk. 

TEST EXEC:

EXEC dbo.WaitForJob
 @job_name = 'Backup All Databases',
 @job_owner = 'sa',
 @delay_interval = '00:01:00'

TO DO LIST:
 1. Nothing yet - pending feedback.

NOTES: 
 Please send any comments or suggestions to dmmaxwell(at)gmail.com.

*****************************************************************
AUTHOR           EXT.    DATE            REASON FOR CHANGE
-----------------------------------------------------------------
David Maxwell    61142   2009.01.26      Initial version.
****************************************************************/
 @job_name varchar(200) = NULL,  -- No job name = FAIL.
 @job_owner varchar(30) = 'sa', -- The job owner.
 @delay_interval char(8) = '00:00:30' -- Delay between checks.
AS

SET NOCOUNT ON

-- We'll be passing this to xp_sqlagent_enum_jobs
DECLARE 
 @job_guid uniqueidentifier,
 @ErrorMessage varchar(4000)

-- No errors yet...
SET @ErrorMessage = (SELECT NULL)

--Validate the input first.
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs
   WHERE [name] = @job_name) = 0
 BEGIN
   SET @ErrorMessage = (SELECT
   'Job name invalid.  Please supply a valid job name.'
   )
   GOTO EXITPROC
 END

-- Drop the temp table, if it already exists.
IF (SELECT OBJECT_ID('tempdb.dbo.#TempJobs')) IS NOT NULL
DROP TABLE #TempJobs

-- Create the temp table for holding the xp_sqlagent_enum_jobs
-- results.
CREATE TABLE #TempJobs (
 JobID int null,
 LastRunDate int null,
 LastRunTime int null,
 NextRunDate int null,
 NextRunTime int null,
 NextSchedule int null,
 Requested bit null,
 ReqSource  int null,
 ReqSourceID varchar(100) null,
 Running bit null,
 CurrentStep  int null,
 CurrentRetry int null,
 JobState int null
 ) 

-- Get the job GUID.
SELECT @job_guid = job_id
FROM msdb.dbo.sysjobs
WHERE name = @job_name

-- Get the info for that job, and insert into the temp table.  
INSERT INTO #TempJobs
EXEC master..xp_sqlagent_enum_jobs 1, @job_owner, @job_guid

IF (SELECT COUNT(*) FROM #TempJobs) IS NULL
 BEGIN
   SET @ErrorMessage = (
   SELECT 'Error getting initial job information.'
   )
   GOTO EXITPROC
 END

---- To test, uncomment this section.
-- IF (SELECT Running FROM #TempJobs) = 1
--   BEGIN
--     PRINT 'Job Is Running.'
--   END
-- ELSE 
--   PRINT 'Job Is Not Running.'
-- GOTO EXITPROC

-- Now start checking every @delay_interval.
-- For testing, comment this section out, and uncomment
-- the one above.
WHILE (SELECT Running FROM #TempJobs) = 1
BEGIN
  TRUNCATE TABLE #TempJobs
  WAITFOR DELAY @delay_interval 
BEGIN TRY
  INSERT INTO #TempJobs
  EXEC master..xp_sqlagent_enum_jobs 1, @job_owner, @job_guid
END TRY
BEGIN CATCH
  SET @ErrorMessage = (SELECT
  'Error during job check loop.' +
  CHAR(13) + ERROR_MESSAGE() + CHAR(13))
  GOTO EXITPROC
END CATCH
END

EXITPROC:
IF (SELECT @ErrorMessage) IS NOT NULL
BEGIN
 RAISERROR(@ErrorMessage,16,1)
 RETURN(-1)
END
ELSE
RETURN(0)