3

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))

6
  • why do not try capture errors and send them inside a single TSQL script?
    – George K
    Jun 6, 2017 at 7:15
  • What error message are you receiving when trying to use the Token in your script? Have you checked the job history? Jun 6, 2017 at 7:40
  • You can capture your errormessage in a table, and then build your e-mail in step 3 through consulting the errormessages table Jun 6, 2017 at 8:31
  • @George.Palacios there is no error message, getting blank
    – Rohaan
    Jun 6, 2017 at 9:39
  • Is Step 1 definitely failing? Jun 6, 2017 at 11:35

2 Answers 2

1

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
1
  • done same thing to achive desired result
    – Rohaan
    Jun 7, 2017 at 13:15
0

Are you using SSIS or is your job running a TSQL script? You question asks about SSIS but your steps imply TSQL?

If it's a script, I'd have thought that a simple TRY CATCH block would be a simpler way of doing this?

3
  • I believe it's actually a SQL Server agent job, based on the description; that matches how those work.
    – RDFozz
    Jun 6, 2017 at 15:42
  • David do you mean: Step 1 includes Try-Catch and the Catch stores data about the error in a table. Step 3 just reads the data from the table. Jun 7, 2017 at 1:16
  • I was more just thinking of doing away with step 3 and having the email sent from within the CATCH block? Jun 8, 2017 at 10:50

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.