Executing SQL Server Agent Job from a stored procedure and returning job result

For all you guys who are not allowed to use the OPENROWSET command, this might help. I found the start for my solution here:

http://social.msdn.microsoft.com/Forums/en-US/89659729-fea8-4df0-8057-79e0a437b658/dynamically-checking-job-status-with-tsql

This relies on the fact that some columns of the msdb.dbo.sysjobactivity table first get populated after the job finishes in one way or the other.

-- Start job
DECLARE @job_name NVARCHAR(MAX) = 'JobName'
EXEC msdb.dbo.sp_start_job @job_name = @job_name


-- Wait for job to finish
DECLARE @job_history_id AS INT = NULL

WHILE @time_constraint = @ok
BEGIN
    SELECT TOP 1 @job_history_id = activity.job_history_id
    FROM msdb.dbo.sysjobs jobs
    INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
    WHERE jobs.name = @job_name
    ORDER BY activity.start_execution_date DESC

    IF @job_history_id IS NULL
    BEGIN
        WAITFOR DELAY '00:00:10'
        CONTINUE
    END
    ELSE
        BREAK
END


-- Check exit code
SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id

You might want to put in some checks for how long the WHILE-loop is allowed to run. I chose to keep that part out of the example.

Microsoft guidance for exit codes etc.: http://technet.microsoft.com/en-us/library/ms174997.aspx

Here is a a script that will check the status of a job and will run it if it is not running already.

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)

DECLARE @job_id uniqueidentifier ;
select @job_id = job_id from msdb.dbo.sysjobs where name = 'Job1';
insert into @xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id

select case when running = 1 then 'Currently Running' else '' end as running, 
case job_state 
    when 0 then 'Not Idle or Suspended'
    when 1 then 'Executing Job'
    when 2 then 'Waiting For Thread'
    when 3 then 'Between Retries'
    when 4 then 'Idle'
    when 5 then 'Suspended'
    when 6 then 'WaitingForStepToFinish'
    when 7 then 'PerformingCompletionActions'
end as job_state
from @xp_results

IF (select running from @xp_results) <> 1
    EXEC msdb.dbo.sp_start_job 'Job1'

@lapponiandevil has the best, most usable solution here but their code is slightly more complex than it needs to be and doesn't actually work as-is since it needs @Time_constraint and @ok variables which aren't defined in the code shown.

These are to support a timeout for the Agent Job, but this isn't necessary. An Agent Job step can be configured with its own timeout value and will error out properly if it exceeds it, unlike their code. If you used that timeout method, you could find yourself hunting for phantom errors or being unaware that the Agent Job you were waiting for was still running as you moved to the next step in your process.

I think cutting the code down to the bare minimum and removing the timeout functionality is ideal. Here's what I came up with based on their solution:

-- Start Agent Job
DECLARE @JobName NVARCHAR(128) = 'My Agent Job Name'
   EXEC msdb.dbo.sp_start_job @JobName

-- Wait for Agent Job to finish
DECLARE @HistoryID AS INT = NULL
  WHILE @HistoryID IS NULL
  BEGIN
        --Read most recent Job History ID for the specified Agent Job name
            SELECT TOP 1 @HistoryID = b.job_history_id
              FROM msdb.dbo.sysjobs a
        INNER JOIN msdb.dbo.sysjobactivity b ON b.job_id = a.job_id
             WHERE a.name = @JobName
          ORDER BY b.Start_execution_date DESC
        --If Job is still running (Job History ID = NULL), wait 3 seconds
                IF @HistoryID IS NULL WAITFOR DELAY '00:00:03'
    END

-- Check Agent Job exit code to make sure it succeeded
     IF (SELECT run_status 
           FROM msdb.dbo.sysjobhistory 
          WHERE instance_id = @HistoryID) <> 1 
  THROW 69000, 'Child Agent Job failure', 1;  

You can run the query:

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'MonthlyData'

It'll return a column run_status. Statuses are:

 0 - Failed
 1 - Succeeded
 2 - Retry
 3 - Canceled         

More info on MSDN

EDIT: You might want to to poll your job and make sure it's executed. You can get this information from sp_help_job procedure. When this procedure returns status of 4 it means the job is idle. Then it's safe to check for it's run status.

You can poll using following code:

DECLARE @job_status INT
SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')

WHILE @job_status <> 4
BEGIN
    WAITFOR DELAY '00:00:03'
    SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')
END

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'NightlyBackups' ;
GO

This code will check for the status, wait for 3 seconds and try again. Once we get status of 4 we know the job is done and it's safe to check for the job history.