SQL Server: Executing Jobs Synchronously

SQL Server jobs can be started from T-SQL using msdb.dbo.sp_start_job:

EXEC msdb.dbo.sp_start_job @job_name = 'My job name'

This call however will return immediately (i.e. the job executes asynchronously). Sometimes it is desirable for the job to be executed synchronously.  The following procedure accomplishes this.

CREATE PROCEDURE execute_job_synchronously @jobID UNIQUEIDENTIFIER, @jobName NVARCHAR(500), @timeoutSeconds INT = 60 AS BEGIN SET NOCOUNT ON DECLARE @start DATETIME DECLARE @end DATETIME DECLARE @status INT IF @jobID IS NULL SELECT @jobID = job_id FROM msdb.dbo.sysjobs WHERE [name] = @jobName SET @start = GETDATE() SET @end = DATEADD(s, @timeoutSeconds, @start) SET @status = 0 EXEC msdb.dbo.sp_start_job @job_id = @jobID WAITFOR DELAY '00:00:01' CREATE TABLE #CURRENT_JOB_STATUS ( [Job ID] UNIQUEIDENTIFIER, [Last Run Date] NVARCHAR(50), [Last Run Time] NVARCHAR(50), [Next Run Date] NVARCHAR(50), [Next Run Time] NVARCHAR(50), [Next Run Schedule ID] NVARCHAR(50), [Requested To Run] NVARCHAR(50), [Request Source] NVARCHAR(50), [Request Source ID] NVARCHAR(50), [Running] NVARCHAR(50), [Current Step] NVARCHAR(50), [Current Retry Attempt] NVARCHAR(50), [State] INT) INSERT INTO #CURRENT_JOB_STATUS EXEC master.dbo.xp_sqlagent_enum_jobs 1, '', @jobID SELECT TOP 1 @status = State FROM #CURRENT_JOB_STATUS WHILE (@status != 4 AND GETDATE() < @end) BEGIN WAITFOR DELAY '00:00:01' TRUNCATE TABLE #CURRENT_JOB_STATUS INSERT INTO #CURRENT_JOB_STATUS EXEC master.dbo.xp_sqlagent_enum_jobs 1, '', @jobID SELECT TOP 1 @status = State FROM #CURRENT_JOB_STATUS END DECLARE @message VARCHAR(1000) IF @status != 4 BEGIN SET @message = 'Timeout (' + CONVERT(VARCHAR(20), @timeoutSeconds) + ' seconds) while waiting for job "' + ISNULL(@jobName, @jobID) + '" to complete' RAISERROR(@message, 16, 1) END ELSE BEGIN SET @message = 'The job "' + ISNULL(@jobName, @jobID) + '" failed' IF 0 = (SELECT last_run_outcome FROM msdb.dbo.sysjobservers WHERE (CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time = (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time) FROM msdb.dbo.sysjobservers WHERE (job_id = @jobID)) AND job_id = @jobID) RAISERROR(@message, 16, 1) END END GO

Usage

Parameters:

• @jobID UNIQUEIDENTIFIER (optional, only required if @jobName is not provided)
• @jobName NVARCHAR(500) (optional, only required if @jobID is not provided)
• @timeoutSeconds INT (optional, default 60 seconds)

Remarks:

• The procedure will throw an error if the timeout period is reached before the job completes
• The procedure will throw an error if the job fails

Examples:

EXEC common.dbo.execute_job_synchronously '2696980E-1E38-43C4-8350-29DBFC56974D', null, 30

EXEC common.dbo.execute_job_synchronously null, ‘My cool job’

EXEC common.dbo.execute_job_synchronously null, ‘My cool job’, 3600

PONG