In SQL Server 2005 and 2008 it’s possible to start an agent job with T-SQL syntax. This is frequently useful. For example, you may have a job that you’d like to have an outside application invoke. Jobs can also be used to execute SSIS packages. Using T-SQL to execute a job that runs an SSIS package is a way to provide an application access to an SSIS package.

One potential hangup with running agent jobs from a stored procedure is that control is returned immediately to the SQL that runs the job. Most of the time, it would be useful to know when the job has finished. Perhaps you’re calling an SSIS package from a web page and you’d like to update the UI when the package has been completed.

I’ll show you how to determine the status of a job that has been invoked, and return upon completion.

First, some background.

Running An Agent Job With T-SQL

The syntax to execute an agent job is as follows:

EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'

This will execute the job specified in the @Job_Name parameter of the sp_start_job function. This works as long as the account running the command has permissions to run agent jobs. If you run this, you’ll notice that the command returns immediately, proceeding to the next line in the batch. This is no good for most uses.

One way of waiting to return until the job has finished is to drop into a tight loop until the job has completed.

Checking The Status Of An Agent Job In T-SQL

Our plan requires us to be able to check the status of an agent job with T-SQL syntax. We can accomplish this with the following syntax:

EXEC MSDB.dbo.sp_help_job @job_name = 'JobName', @job_aspect = 'JOB'

This will return a record set containing information about our job. The most interesting piece of information for us is the current execution status. When the job is idle sp_help_job will return a value of 4 for the current execution status. If you’re interested in what the other values mean, you can dig through the definition of MSDB.dbo.sp_help_job, which will in turn send you digging through MSDB.dbo.sp_get_composite_job_info.

The Wait Loop

Now that we know how to invoke our agent job, and check its status we can start putting it all together.

Our procedure will be something like this:

Call Agent Job
While Execution Status  4
(
	Poll Execution Status
)

Or, in actual T-SQL:

DECLARE @JobStatus INT
SET @JobStatus = 0
EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'
SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 
                          'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')     
WHILE @JobStatus  4
BEGIN	
	SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 
                                'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')	
END

Getting the job status from the sp_help_job stored procedure requires us to encapsulate it in an OPENROWSET to provide a rowset we can select from. There are other ways to do this, if you’re averse to using OPENROWSET. I’ll leave them up to you.

The Final Version

Running the query above, you’ll notice that it seems to bounce out of the WHILE loop way too quickly. In fact the WHILE loop doesn’t even make one iteration. When a job is invoked by sp_start_job the current_execution_status in sp_help_job is not updated instantaneous. It’s updated very quickly, but it’s possible for your initial query setting @JobStatus to get the ‘4′ (Idle) value immediately after you’ve kicked the job off. The only way I’ve seen to get around this is to add a WAITFOR command with the DELAY argument in between sp_start_job and the initial sp_help_job check. We can also add a WAITFOR DELAY in the wait loop to prevent the loop from becoming too inefficient. You can tune the WAITFOR value depending on what your job is doing.

DECLARE @JobStatus INT

SET @JobStatus = 0     

EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'

WAITFOR DELAY '00:00:01'

SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  
                         'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')

 WHILE @JobStatus  4

BEGIN

	WAITFOR DELAY '00:00:03'

	SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  
                                'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')	

END

There are still more improvements that could be made. Perhaps you would like to determine what the other current_execution_status codes are and do something when one of them is detected. One suggestion I’d make is to add a timeout value to the WHILE loop to prevent it from waiting indefinitely if something unexpected happens.
I’ll leave that exercise to the reader.