This isn't pretty but it works well - I just threw it into a Job running hourly

CREATE PROCEDURE [dbo].[utl_dba_CheckSQLJobsForErrors]

@notifyEmailGroup varchar(300) = 'dba_alerts@company.com'

AS /*

USED TO MONITOR MSDB JOB TABLES AND SEND AN

EMAIL ALERT WHEN AN ERROR HAS OCCURED ON A JOB STEP

Looks 20 Minutes back for Job Errors - This makes sure that it will send multiple emails

*/

DECLARE @sqlSelect nvarchar(500)

CREATE TABLE #SQLAGENTERRORS (

[id] int IDENTITY(1, 1)

NOT NULL,

[SQL AGENT JOB NAME] [sysname] NOT NULL,

[step_id] [int] NOT NULL,

[step_name] [sysname] NOT NULL,

[sql_message_id] [int] NOT NULL,

[sql_severity] [int] NOT NULL,

[message] [nvarchar](1024) NULL,

[run_status] [int] NOT NULL,

[run_date] [int] NOT NULL,

[run_time] [int] NOT NULL,

[run_duration] [int] NOT NULL,

[retries_attempted] [int] NOT NULL,

[server] [sysname] NOT NULL)

SET @sqlSelect = '

SELECT DISTINCT

a.[Name][SQL AGENT JOB NAME],[step_id],[step_name],0[sql_message_id]'

+ ',0[sql_severity],0[message],0[run_status],[run_date],[run_time],0[run_duration] '

+ ', 0 [retries_attempted],[server]

FROM [msdb].[dbo].[sysjobs] as a

JOIN [msdb].[dbo].[sysjobhistory] as b on

a.job_id=b.job_id ' + 'WHERE

b.Sql_Severity <> 0

AND run_date = REPLACE(CONVERT(VARCHAR(10),GETDATE(),121),''-'','''') ' + 'AND [run_time] >= '''

+ REPLACE(CONVERT(varchar(20), DATEADD(minute, -20, GETDATE()), 08), ':', '') + ''''

+ ' ORDER BY [run_date],a.[Name],b.[step_id] '

-- WRITE TEMP TABLE TO FORMAT DATA

INSERT INTO #SQLAGENTERRORS

EXECUTE MASTER..SP_EXECUTESQL @sqlSelect

IF (SELECT

COUNT(1)

FROM

#SQLAGENTERRORS) > 0

BEGIN

DECLARE

@calldate datetime,

@sqlStatement nvarchar(350),

@subject varchar(128),

@body varchar(4000)

SET @body = '

'

SET @subject = 'Job Step Failures Detected ' + CONVERT(varchar(25), GETDATE(), 120)

IF (SELECT

COUNT(1)

FROM

#SQLAGENTERRORS) > 0

BEGIN

DECLARE

@Run_Date varchar(12),

@Run_Time varchar(12),

@SQLAGENTJOBNAME varchar(200),

@step_id varchar(12),

@step_name varchar(200),

@sql_message_id varchar(12),

@sql_severity varchar(12),

@message varchar(500),

@run_status varchar(12),

@run_duration varchar(12),

@retries_attempted varchar(12),

@server varchar(25),

@id int,

@maxID int

SELECT

@id = 1,

@maxID = MAX(ID)

FROM

#SQLAGENTERRORS

SELECT

*

FROM

#SQLAGENTERRORS

WHILE @id <= @maxID

BEGIN

SELECT

@Run_Date = RTRIM(SUBSTRING(CONVERT(varchar(8), [run_date]), 1, 4) + '-'

+ SUBSTRING(CONVERT(varchar(8), [run_date]), 5, 2) + '-'

+ SUBSTRING(CONVERT(varchar(8), [run_date]), 7, 2)),

@Run_Time = CASE WHEN LEN([run_time]) = 5

THEN RTRIM('0' + SUBSTRING(CONVERT(varchar(10), [run_time]), 1, 1) + ':'

+ SUBSTRING(CONVERT(varchar(10), [run_time]), 2, 2) + ':'

+ SUBSTRING(CONVERT(varchar(10), [run_time]), 4, 2))

ELSE RTRIM(SUBSTRING(CONVERT(varchar(10), [run_time]), 1, 2) + ':'

+ SUBSTRING(CONVERT(varchar(10), [run_time]), 3, 2) + ':'

+ SUBSTRING(CONVERT(varchar(10), [run_time]), 5, 2))

END,

@SQLAGENTJOBNAME = RTRIM([SQL AGENT JOB NAME]),

@step_id = RTRIM([step_id]),

@step_name = RTRIM([step_name]),

@sql_message_id = RTRIM([sql_message_id]),

@sql_severity = RTRIM([sql_severity]),

@message = RTRIM([message]),

@run_status = RTRIM([run_status]),

@run_duration = RTRIM([run_duration]),

@retries_attempted = RTRIM([retries_attempted]),

@server = RTRIM([server])

FROM

#SQLAGENTERRORS

WHERE

id = @id

SELECT

@body = @body + 'ERROR #' + RTRIM(CONVERT(varchar(10), @id)) + ' - ' + @SQLAGENTJOBNAME

+ ' on ' + @server + '

RUN DATE: ' + @Run_Date + ' Run Time: ' + @Run_Time + '

=======================================

Step: ' + @step_id + ' - ' + @step_name + '

'

SET @id = @id + 1

END

EXEC msdb..sp_send_dbmail @recipients = @notifyEmailGroup, @subject = @subject, @body = @body

END

END

DROP TABLE #SQLAGENTERRORS