Notifications for SQL Job when a step fails

Question

Trying to find out if there is a way to setup email notifications when just 1 (or more) steps fail on a sql job. I have a job with 9 steps, but each step needs to be set to continue to next step even if it fails. So even when a step fails, because I need it to go to the next step its not set to "report failure". As SQL Server Agent does not have an option for "Report failure and go to next step" I am wondering if anyone has any workarounds for getting a notification

Answer 1

You can add an additional job step to query the msdb tables for steps that failed. After this, the sp_send_dbmail stored procedure can be used to send an email with the errors if any occurred. The run_date column of SYSJOBHISTORY is an int column, so the date will be along the format of YYYYMMDD. The filter below on the run_status column will apply to individual steps regardless of the overall job outcome, with 0 indicating a status of failed.

DECLARE @JobExecutionDate INT 

--current date
SET @JobExecutionDate = CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT) 


--check if there were any errors first
IF EXISTS (SELECT h.instance_id
FROM MSDB.DBO.SYSJOBHISTORY h 
         INNER JOIN MSDB.DBO.SYSJOBS j ON h.job_id = j.job_id 
         INNER JOIN MSDB.DBO.SYSJOBSTEPS s ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE h.run_status = 0 AND h.run_date = @JobExecutionDate AND j.name = 'YourJobName')

BEGIN

DECLARE @Title VARCHAR(50)

SET @Title = CONCAT(CONVERT(VARCHAR(12), CAST(GETDATE() AS DATE), 109), ' Job Error Email')


EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Your Database Mail Profile',  
    @recipients = '[email protected]',  
@query = 'SELECT  j.[name] AS JobName,  
         s.step_name AS StepName,  
         h.run_date AS RunDate, 
         h.run_time AS RunTime, 
         h.sql_severity As ErrorSeverity, 
         h.message AS ErrorMessage
FROM MSDB.DBO.SYSJOBHISTORY h 
         INNER JOIN MSDB.DBO.SYSJOBS j ON h.job_id = j.job_id 
         INNER JOIN MSDB.DBO.SYSJOBSTEPS s ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE h.run_status = 0 AND h.run_date = CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT) AND j.name = ''YourJobName''
',
  @query_result_no_padding = 1,
    @subject = @Title ;


END