A Stored Procedure to Launch a SQL Agent Job and Wait

If you need to launch a SQL Agent Job from within a stored procedure you can simply use msdb..sp_start_job @job. But what if you need to wait for that job to finish before you can continue whatever you are doing in that stored procedure ? Here is the code for a stored procedure that does exactly that: it launches a SQL Agent job and waits for it to finish. The name of the job and the maximum time to wait are its input parameters.

-- =============================================
-- Author:		TVdP
-- Create date: 20090706
-- Description:	Starts a SQLAgent Job and waits for it to finish or until a specified wait period elapsed
-- @result:	1 -> OK
--			0 -> still running after maxwaitmins
-- =============================================
CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) --, @result int output)
as begin

set NOCOUNT ON;
set XACT_ABORT ON;

	BEGIN TRY

	declare @running as int
	declare @seccount as int
	declare @maxseccount as int
	declare @start_job as bigint
	declare @run_status as int

	set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

	set @maxseccount = 60*@maxwaitmins
	set @seccount = 0
	set @running = 0

	declare @job_owner sysname
	declare @job_id UNIQUEIDENTIFIER

	set @job_owner = SUSER_SNAME()

	-- get job id
	select @job_id=job_id
	from msdb.dbo.sysjobs sj
	where sj.name=@job

	-- invalid job name then exit with an error
	if @job_id is null
		RAISERROR (N'Unknown job: %s.', 16, 1, @job)

	-- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
	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)

	-- start the job
	declare @r as int
	exec @r = msdb..sp_start_job @job

	-- quit if unable to start
	if @r<>0
		RAISERROR (N'Could not start job: %s.', 16, 2, @job)

	-- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
	WAITFOR DELAY '0:0:01';
	set @seccount = 1

	-- check job run state
	insert into @xp_results
	execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

	set @running= (SELECT top 1 running from @xp_results)

	while @running<>0 and @seccount < @maxseccount
	begin
		WAITFOR DELAY '0:0:01';
		set @seccount = @seccount + 1

		delete from @xp_results

		insert into @xp_results
		execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

		set @running= (SELECT top 1 running from @xp_results)
	end

	-- result: not ok (=1) if still running

	if @running <> 0 begin
		-- still running
		return 0
	end
	else begin

		-- did it finish ok ?
		set @run_status = 0

		select @run_status=run_status
		from msdb.dbo.sysjobhistory
		where job_id=@job_id
		  and cast(run_date as bigint) * 1000000 + run_time >= @start_job

		if @run_status=1
			return 1  --finished ok
		else  --error
			RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

	end

	END TRY
	BEGIN CATCH

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage =
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
            'Message: '+ ERROR_MESSAGE();

    RAISERROR
        (
        @ErrorMessage,
        @ErrorSeverity,
        1,
        @ErrorNumber,    -- original error number.
        @ErrorSeverity,  -- original error severity.
        @ErrorState,     -- original error state.
        @ErrorProcedure, -- original error procedure name.
        @ErrorLine       -- original error line number.
        );

	END CATCH

end

Edited 20/11/2009: better error handling in case job did not finish well.

15 Comments »

Comment by Prophix Software
2010-07-16 19:42:52

Very helpful article. Thanks much for putting time into this. hope this lands you an awesome job!

 
Comment by dmast
2010-09-01 09:07:57

Thats’ what i needed!
Thank you.

 
Comment by Herman
2010-12-07 17:18:10

This code rocks !!! Exactly what I needed. Thanks a lot!

 
Comment by Max Awesome
2011-01-21 04:51:51

Very helpful article! However, I noticed a couple things that might improve it.

1. Your WHILE loop already contains:

insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

set @running= (SELECT top 1 running from @xp_results)

So, you can simply add ‘SET @running=0′ immediately before entering the WHILE loop and let the loop take care of this ‘insert exec’ and ’set’ portion.

Otherwise, you are essentially simulating the first “loop” of the WHILE loop outside the loop, and that’s just plain loopy. :D

2. Because you’re now relying on your loop to do what it’s supposed to do, you can remove the first ‘WAITFOR DELAY’ statement and @seccount increment (the one outside the loop), as we’ve got one baked into our loop already.

3. I had to use the following to identify my job in the results of xp_sqlagent_enum_jobs:

INSERT INTO @Results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobOwnerName
SET @IsRunning = (SELECT TOP 1 running FROM @Results WHERE job_id=@JobId)

 
Comment by Max Awesome
2011-01-21 05:06:57

Oop! Typo! In my above post, it should read:

SET @running=1

Comment by Tom
2011-02-23 18:19:20

This post helped me alot! Thanks all.

To Max,

Can you post the complete stored procedure.

Thanks

 
 
Comment by John Morales
2012-04-02 19:57:05

– =============================================
– Author: TVdP
– Create date: 20090706
– Description: Starts a SQLAgent Job and waits for it to finish or until a specified wait period elapsed
– @result: 1 -> OK
– 0 -> still running after maxwaitmins
– Changes: John Morales 4/2/2012 – Updated script so that it doesn’t depend on whether the job starts in the first second.
– =============================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[StartAgentJobAndWait]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [dbo].[StartAgentJobAndWait]
GO

CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) –, @result int output)
as begin

set NOCOUNT ON;
set XACT_ABORT ON;

BEGIN TRY

declare @running as int
declare @seccount as int
declare @maxseccount as int
declare @start_job as bigint
declare @run_status as int
declare @last_run_date as int
declare @last_run_time as int

set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

set @maxseccount = 60*@maxwaitmins
set @seccount = 0
set @running = 0

declare @job_owner sysname
declare @job_id UNIQUEIDENTIFIER

set @job_owner = SUSER_SNAME()

– get job id
select @job_id=job_id
from msdb.dbo.sysjobs sj
where sj.name=@job

– invalid job name then exit with an error
if @job_id is null
RAISERROR (N’Unknown job: %s.’, 16, 1, @job)

– output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
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)
– check job run state
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

select @last_run_date = ISNULL(last_run_date, 0), @last_run_time = ISNULL(last_run_time, 0) from @xp_results

– start the job
declare @r as int
exec @r = msdb..sp_start_job @job

– quit if unable to start
if @r0
RAISERROR (N’Could not start job: %s.’, 16, 2, @job)

WHILE NOT EXISTS (SELECT 1 FROM @xp_results where last_run_date > @last_run_date or last_run_time > @last_run_time) AND @seccount < @maxseccount
BEGIN
RAISERROR('Waiting for %s to finish, time elasped %d sec', 0, 1, @job, @seccount) WITH NOWAIT;
WAITFOR DELAY '0:0:01';
set @seccount = @seccount + 1
insert into @xp_results
execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
END

set @running = (SELECT top 1 running from @xp_results)

– result: not ok (=1) if still running

if @running 1 begin
– still running
return 1
end
else begin

– did it finish ok ?
set @run_status = 0

select @run_status=run_status
from msdb.dbo.sysjobhistory
where job_id=@job_id
and cast(run_date as bigint) * 1000000 + run_time >= @start_job

if @run_status=1
return 0 –finished ok
else –error
RAISERROR (N’Job %s did not finish successfully.’, 16, 2, @job)

end

END TRY
BEGIN CATCH

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ‘-’);

SELECT @ErrorMessage =
N’Error %d, Level %d, State %d, Procedure %s, Line %d, ‘ +
‘Message: ‘+ ERROR_MESSAGE();

RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, — original error number.
@ErrorSeverity, — original error severity.
@ErrorState, — original error state.
@ErrorProcedure, — original error procedure name.
@ErrorLine — original error line number.
);

END CATCH

end

 
Comment by Mustafa Salman
2012-09-26 07:31:07

Thank you very much
That is Exact what I want to do ….
Thanks again

 
Comment by Patti
2012-10-22 16:39:42

Can I use this script to execute a two step job that the first step must run before the second step does? I have a two step job. The first step kicks off a sql agent job maintenance plan backup. The second step kicks off a sql agent job that has multiple steps that runs a powershell copy of the backup files for more than one directory to another server. Then there are powershell steps within this job that delete the backup files from the source and then the last step is a powershell that deletes old backups at the destination directory.

I tried to make a sql maintenance plan with a prescedence constraint that kicks off the sql agent jobs but it appears that the second job ran before the backups were finished.

 
Comment by Nawar
2012-11-14 06:07:33

It is amazing really but i get he the below errors, anyone can help please:

Server: Msg 170, Level 15, State 1, Procedure StartAgentJobAndWait, Line 14
Line 14: Incorrect syntax near ‘TRY’.
Server: Msg 197, Level 15, State 1, Procedure StartAgentJobAndWait, Line 73
EXECUTE cannot be used as a source when inserting into a table variable.
Server: Msg 197, Level 15, State 1, Procedure StartAgentJobAndWait, Line 85
EXECUTE cannot be used as a source when inserting into a table variable.
Server: Msg 170, Level 15, State 1, Procedure StartAgentJobAndWait, Line 111
Line 111: Incorrect syntax near ‘TRY’.
Server: Msg 195, Level 15, State 1, Procedure StartAgentJobAndWait, Line 123
‘ERROR_NUMBER’ is not a recognized function name.
Server: Msg 195, Level 15, State 1, Procedure StartAgentJobAndWait, Line 131
‘ERROR_MESSAGE’ is not a recognized function name.
Server: Msg 156, Level 15, State 1, Procedure StartAgentJobAndWait, Line 145
Incorrect syntax near the keyword ‘END’.

Comment by Tom VdP
2012-11-14 10:03:26

@Nawar: Is it possible this is on a pre-2005 sql server ? It does not know the TRY…CATCH construct.

 
 
Comment by Parvaneh
2012-11-22 11:36:10

Thank you !

 
Comment by JFB
2013-10-31 15:37:37

Thanks for your post, it helped me get started. Here is one that I wrote which does pretty much the same thing.

CREATE procedure [dbo].[StartAgentJobAndWait]
@jobName nvarchar(128) = N’Weekly_ParlData BUILD in Dev’
,@maxWaitSeconds int = 360
AS

BEGIN

DECLARE @runDate int
,@runTime int
,@waitForJob int = 1
,@secondsCount int
,@jobId UNIQUEIDENTIFIER
,@runStatus int

– Get the job Id
SELECT
@jobId = job_id
FROM
msdb.dbo.sysjobs
WHERE
sysjobs.name = @jobName

IF @jobId IS NULL BEGIN
– Job not found
RAISERROR (N’Unknown job: %s.’, 16, 1, @jobName)
END

– START the job
DECLARE @Result int
EXEC @Result = msdb..sp_start_job @jobName

IF @Result 0 BEGIN
– Unable to start the job so quit
RAISERROR (N’Could not start job: %s.’, 16, 2, @jobName)
END

– Wait 10 seconds before checking if job is still running

WAITFOR DELAY ‘0:0:10′;
SET @secondsCount = 10

SELECT TOP 1
@runDate = sysjobhistory.run_date
,@runTime = sysjobhistory.run_time
FROM
msdb..sysjobhistory
WHERE
job_id = @jobId
AND step_id = 1
ORDER BY
Run_date DESC,
run_time DESC,
instance_id DESC

WHILE (@waitForJob = 1 AND @secondsCount < @maxWaitSeconds) BEGIN

SELECT TOP 1
@runStatus = run_status
FROM
msdb..sysjobhistory
WHERE
job_id = @jobId
AND run_date = @runDate
AND run_time = @runTime
AND step_id = 0

IF @runStatus IS NOT NULL BEGIN
SET @waitForJob = 0
END

PRINT 'Job is still running…Waiting another 5 seconds.'
WAITFOR DELAY '0:0:05';
SET @secondsCount = @secondsCount + 5

END

IF @runStatus IS NULL BEGIN
RAISERROR (N'The Job [%s] is still running.', 16, 2, @jobName)
END ELSE IF @runStatus = 0 BEGIN –Failed
RAISERROR (N'The Job [%s] failed.', 16, 2, @jobName)
END ELSE IF @runStatus = 1 BEGIN –Success
PRINT 'The job finished successfully'
END

END

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post