Return SQL Agent Job status in SSRS when executing job from SSRS
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'
EXEC msdb.dbo.sp_start_job 'Run selected job'
IF @Job = 'Delete Previous Run' AND @Confirm = 'DELETE'
EXEC msdb.dbo.sp_start_job 'Delete Previous Run'
When I put
EXECstatement, the only thing that is returned is
Job '' completed successfully
I'm hoping to return the SQL Agent status as soon as the job completes. How can I do that?
Analeea last edited by
sp_start_jobprocedure 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_jobwill give you the job status, but it uses
#temptables, which can cause problems in your code if you try to insert the results of
sp_help_jobinto 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, https://am2.co/2016/02/xp_sqlagent_enum_jobs_alt/ .
Each time around that loop, you would do something like this to get the job status (or grab https://github.com/amtwo/dba-database/blob/production/functions-tvfs/dbo.AgentJob_Status.sql from https://am2.co/dbadb , which I describe further in https://am2.co/2016/02/xp_sqlagent_enum_jobs_alt/
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
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'
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;