This does not answer your question, but you might find it useful. We have some jobs with multiple steps and if a middle step fails (eg step 4), the job continues to the next step (step 5, intentionally). However, if the final step succeeds the job itself shows as completed normally, not failed, so I would not get an email letting me know there was a problem.

I wrote this stored procedures to send me emails no matter what step fails. I think it is more useful than built in job notification.

I also have similar steps for my remote servers using LinkedServer.

CREATE PROCEDURE [dbo].[usp_JobStepFailedAlert] AS

-- failed job steps

declare @RecCount int

DECLARE @tableHTML NVARCHAR(MAX) ;

declare @SubjectText varchar(150)

declare @BodyText varchar(150)

--=============================================================================

----- LOCAL SERVER

--=============================================================================

set @SubjectText = 'Failed Job Steps - MyServer ' + CONVERT(varchar(32), GETDATE(), 101) + ' @ ' + CONVERT(varchar(32), GETDATE(), 108)

set @BodyText = ' '

set @RecCount = 0

-- Get count of failed steps for specified time period

set @RecCount = (SELECT count(*)

FROM [msdb].[dbo].[sysjobhistory] h

join msdb..sysjobs j on j.job_id = h.job_id

where msdb.dbo.agent_datetime(h.run_date,h.run_time) > getdate() -1

and run_status <> 1 -- not succeeded

and h.step_id > 0)-- only look at actual steps

-- At least 1 failed step, so send email with query results showing job step info.

if @RecCount > 0 begin

SET @tableHTML = @BodyText +

N'<H1>Failed Job Steps</H1>' +

N'<table border="1">' +

N'<tr><th>Job Name</th><th>Step#</th><th>Step Name</th>' +

N'<th>Run Date</th><th>Server</th></tr>' +

CAST ( ( select td = j.name ,'', td = cast(step_id as char(7)), ' ',

td = step_name , '',

td = cast((msdb.dbo.agent_datetime(h.run_date, h.run_time)) as char(23)) , ' ' ,

td = [server] , ' '

FROM [msdb].[dbo].[sysjobhistory] h

join msdb..sysjobs j on j.job_id = h.job_id

where msdb.dbo.agent_datetime(h.run_date,h.run_time) > getdate() -1

and run_status <> 1 -- not succeeded

and h.step_id > 0-- only look at actual steps

order by run_date desc, run_time desc, j.name, step_id

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA_Alerts',

@recipients= 'Me@MyCompany.com',

@subject = @SubjectText,

@body = @tableHTML,

@body_format = 'HTML',

@importance = 'High';

end