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