Use polaris DROP TABLE IF EXISTS #FullDelta DECLARE @FinalDate INT; SET @FinalDate = CONVERT(int , CONVERT(varchar(10), DATEADD(DAY, -1, GETDATE()), 112) ) -- Yesterday's date as Integer in YYYYMMDD format IF (SELECT COUNT(*) FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE h.run_status = 0 -- Failure AND h.step_id != 0 AND ((h.run_date = @FinalDate AND h.run_time > 60000) OR h.run_date > @FinalDate) AND j.name NOT IN ( 'Cataloging Background Tasks' ) ) >= 1 BEGIN CREATE TABLE #FullDelta ( Job_Name varchar(max), Step_ID int, Step_Name Varchar(max), Run_Date datetime, SQL_Severity int, [Message] VARCHAR(max) ) INSERT INTO #FullDelta (Job_Name, Step_ID, Step_Name, Run_Date, SQL_Severity, [Message]) SELECT j.[name], h.step_id, s.step_name, msdb.dbo.agent_datetime(h.run_date,h.run_time) run_date, h.sql_severity, h.message FROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id LEFT JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id WHERE h.run_status = 0 -- Failure AND h.step_id != 0 AND ((h.run_date = @FinalDate AND h.run_time > 60000) OR h.run_date > @FinalDate) AND j.name NOT IN ( 'Cataloging Background Tasks' ) ORDER BY msdb.dbo.agent_datetime(h.run_date,h.run_time) asc, j.[name] DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml = CAST(( SELECT 'top' AS "td/@valign", [Job_Name]AS 'td','', 'top' AS "td/@valign", [Step_ID] AS 'td','', 'top' AS "td/@valign", [Step_Name] AS 'td','', 'top' AS "td/@valign", [Run_Date] AS 'td','', 'top' AS "td/@valign", [SQL_Severity] AS 'td','', 'top' AS "td/@valign", [Message] AS 'td' FROM  #FullDelta ORDER BY Run_Date ASC, Job_Name FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='

Failed Jobs & Job Steps from Yesterday

This alert is generated if any jobs and/or job steps failed yesterday. Note that the SQL_Severity does not actually align to code from Microsoft, it''s a number that aligns to the message.


'   SET @body = @body + @xml +'
Job_Name Step_ID Step_Name Run_Date SQL_Severity Message
' exec msdb.dbo.sp_send_dbmail @profile_name = '', -- the email profile set-up on SQL Server @recipients = '', -- recipient emails, separated by a semi-colon @subject = 'Failed Jobs / Job Steps', @body = @body, @body_format = 'HTML'; DROP TABLE #FullDelta END