0

I'm calling a SQL Agent job from SSRS and using a parameter to determine which job to run.

I can run the job(s) just fine. What I'm looking to do is return a message back to the user based on the job status.

Here is how it's executed:

IF @Job = 'Run'

BEGIN

EXEC msdb.dbo.sp_start_job 'Run selected job'
 
END

IF @Job = 'Delete Previous Run' AND @Confirm = 'DELETE'

BEGIN 

EXEC msdb.dbo.sp_start_job 'Delete Previous Run'

END

When I put PRINT into the code after the EXEC statement, the only thing that is returned is Job '<job name>' completed successfully

I'm hoping to return the SQL Agent status as soon as the job completes. How can I do that?

1
  • 1
    You can query job's history. Have a look at mdsb.dbo.sysjobhistory
    – McNets
    Feb 28, 2022 at 16:26

1 Answer 1

2

The sp_start_job procedure starts the job, then returns to you while the job runs asynchronously. It does not wait for the job to run--just starts it & leaves.

If you want to have your SQL sit & wait for the job to run, you would need to introduce a loop in your T-SQL where you WAITFOR DELAY 'HH:MI:SS', then check the job status, and repeat until the job reaches a terminal status that you can return to the user.

sp_help_job will give you the job status, but it uses #temp tables, which can cause problems in your code if you try to insert the results of sp_help_job into a temp table yourself, you'll hit error 8164, "An INSERT EXEC statement cannot be nested." Some people will use xp_sqlagent_enum_jobs, an undocumented, internal procedure that will return similar results. However, because it is undocumented, I prefer not to use it in production code, since the behavior could change in a future version or CU of SQL Server.

Instead, I prefer to query the tables directly in msdb to determine the job status.

Each time around that loop, you would do something like this to get the job status (or grab the function from my open-source DBA Database, which I describe further in my blog post ):


DECLARE @JobName nvarchar(200) = N'';

SELECT TOP 1
    JobName        = j.name,
    IsRunning      = CASE
                       WHEN ja.job_id IS NOT NULL
                           AND ja.stop_execution_date IS NULL
                         THEN 1 ELSE 0 
                       END,
    JobLastOutcome = CASE
                       WHEN ja.job_id IS NOT NULL
                           AND ja.stop_execution_date IS NULL THEN 'Running'
                       WHEN jh.run_status = 0 THEN 'Failed'
                       WHEN jh.run_status = 1 THEN 'Succeeded'
                       WHEN jh.run_status = 2 THEN 'Retry'
                       WHEN jh.run_status = 3 THEN 'Cancelled'
                     END
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja 
    ON ja.job_id = j.job_id
       AND ja.run_requested_date IS NOT NULL
       AND ja.start_execution_date IS NOT NULL
LEFT JOIN msdb.dbo.sysjobsteps js
    ON js.job_id = ja.job_id
       AND js.step_id = ja.last_executed_step_id
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
       AND jh.instance_id = ja.job_history_id
WHERE j.name = @JobName
ORDER BY ja.start_execution_date DESC;
1
  • Thanks for this. Was hoping I could do something without putting a delay into my code
    – jdids
    Feb 28, 2022 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.