SQL Server Agent – How to Capture Error Message from One Step in Another Step

sql serversql-server-agent

Is it possible to capture error message from one step in another step of a SQL Server Agent job?

I have below scenario:

Step 1: Transact-SQL script (TSQL)
-- On Success action: Go to step 2
-- On Failure action: Go to step 3
Step 2: Transact-SQL script (Send success email)
Step 3: Transact-SQL script (Send failure email)

What I need is, if step 1 fails, I need to capture error message from step 1 in step 3 and send the same in an email notification.

I tried to use below token in Job step, but no success.

declare @err_msg as nvarchar(4000) = $(ESCAPE_SQUOTE(A-MSG))

Best Answer

I think your best bet is going to be querying the msdb.dbo.sysjobhistory table in the job step to get the information you're looking for.

Here's an example query that will get you all runs for a given job (mashed together from these two MSSQLTips articles [ref1], [ref2]). Obviously, you'll want to adjust this to fit your means, but this should provide you with sufficient direction to get what you want.

SELECT j.NAME AS 'JobName'
    ,s.step_id AS 'Step'
    ,s.step_name AS 'StepName'
    ,msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
    ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60) AS 'RunDurationMinutes'
    ,CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
     END AS ExecutionStatus
    ,h.message MessageGenerated
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
WHERE j.enabled = 1 --Only Enabled Jobs
    and j.name = 'JobNameHere'
ORDER BY JobName
    ,RunDateTime DESC
Related Question