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'
    

    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 '' completed successfully

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



  • 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, 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
    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;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2