SendJobFailureReport

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME DECLARE @RoleDesc NVARCHAR(60) SELECT @RoleDesc = a.role_desc FROM sys.dm_hadr_availability_replica_states AS a JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id WHERE b.replica_server_name = @ServerName IF @RoleDesc = 'PRIMARY' BEGIN DECLARE @Document NVARCHAR(MAX) , @DocumentHead NVARCHAR(MAX) , @DocumentTail NVARCHAR(MAX) , @TableHead NVARCHAR(MAX) , @TableBody NVARCHAR(MAX) , @TableTail NVARCHAR(MAX) , @Now DATETIME SET @DocumentHead = N'' + N'' + N'' + N'' + N'

' + N'The following Jobs failed in FPSQL1Listener SQL Server' + N'

'; SET @DocumentTail = ''; SET @TableHead = N'' + N'' + N' ' + N' ' + N' ' + N' ' + N' ' + N''; SET @TableTail = '

Server Name	Job Name	Job Failed DateTime	Status	Error Message

'; SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow], T1.server AS [TD], SUBSTRING(T2.name,1,140) AS [TD], msdb.dbo.agent_datetime(run_date, run_time) as [TD], CASE T1.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS [TD], T1.message AS [TD] FROM msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id WHERE T1.run_status NOT IN (1, 4) AND T1.step_id != 0 AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112) FOR XML RAW('tr'), ELEMENTS); --SELECT @TableBody IF @TableBody IS NULL BEGIN PRINT 'No Jobs failed'; RETURN; END ELSE BEGIN -- Replace the entity codes and row numbers SET @TableBody = Replace(@TableBody, '_x0020_', space(1)) SET @TableBody = Replace(@TableBody, '_x003D_', '=') SET @TableBody = Replace(@TableBody, '1', '') SET @TableBody = Replace(@TableBody, '0', ''); SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default' , @recipients= 'ABC@ABC.com' , @subject = 'Failed SQL Server Jobs on SQL' , @body = @Document , @body_format = 'HTML'; END END; ELSE BEGIN RETURN END