I know this is an old post but give this a try and just plug in your variables as needed in TSQL. The key here is to put this as the very last step of each individual SQL agent job but each job step above it needs to go to NEXT STEP whether it is FAILURE or SUCCESS... Works for me just fine for the most part but please do report any issues you run across. We're on SQL Server 2008 R2 so this is where it's used where I've set it up currently.

SELECT step_name, message FROM msdb.dbo.sysjobhistory WHERE instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0) AND job_id = $(ESCAPE_SQUOTE(JOBID)) AND run_status <> 1 -- success

IF @@ROWCOUNT <> 0 BEGIN RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID))) DECLARE @email_profile NVARCHAR(256) = 'SQLServer Alerts' DECLARE @emailrecipients NVARCHAR(500) = 'EmailAddr@email.com' DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out SELECT sjh.[server] ,sj.NAME ,sjh.step_id ,sjh.[message] ,sjh.run_date ,sjh.run_time INTO #TempJobFailRpt FROM msdb..sysjobhistory sjh INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id) WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112)) AND run_status != 4 -- Do not show status of 4 meaning in progress steps AND run_status != 1 -- Do not show status of 1 meaning success AND NAME = @job_name ORDER BY run_date

IF EXISTS ( SELECT * FROM #TempJobFailRpt ) BEGIN

-----Build report to HTML formatted email using FOR XML PATH DECLARE @tableHTML NVARCHAR(MAX) = '

' + @msgbodynontable + '

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile ,@recipients = @emailrecipients ,@subject = @subject ,@body = @tableHTML ,@body_format = 'HTML'

--Drop Temp table DROP TABLE #TempJobFailRpt END ELSE BEGIN PRINT '*** No Records Generated ***' DROP TABLE #TempJobFailRpt END END

Give this a try and just plug in your variables as needed in TSQL. The key here is to put this as the very last step of each individual SQL agent job but each job step above it needs to go to NEXT STEP whether it is FAILURE or SUCCESS... Works for me just fine for the most part but please do report any issues you run across. We're on SQL Server 2008 R2 so this is where it's used where I've set it up currently.

SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
BEGIN
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLServer Alerts'
DECLARE @emailrecipients NVARCHAR(500) = '[email protected]'
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

IF EXISTS (
        SELECT *
        FROM #TempJobFailRpt
        )
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<body>
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
            ****** 
            Failure occurred in the SQL Agent job named: ''' + @job_name + ''' in at least one of the steps. 
            Below is the job failure history detail for ALL runs of this job today without needing to connect to SSMS to check.
            ******
        </caption>
        
<tr>
    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = run_date
                ,''
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
END
END