USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[utl_dba_CheckSQLJobsForErrors]
	@notifyEmailGroup VARCHAR(300) ='dba_alerts@company.com'
AS 

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

INSERT INTO #SQLAGENTERRORS
EXECUTE [LINKSERVER].MASTER..SP_EXECUTESQL @sqlSelect