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'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'The following Jobs failed in FPSQL1Listener SQL Server'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Server Name</th>'
+ N' <th>Job Name</th>'
+ N' <th>Job Failed DateTime</th>'
+ N' <th>Status</th>'
+ N' <th>Error Message</th>'
+ N'</tr>';
SET @TableTail = '</table>';
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, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
, @recipients= 'SQL1Operator@frontpointsecurity.com'
, @subject = 'Failed SQL Server Jobs on SQL1'
, @body = @Document
, @body_format = 'HTML';
END
END;
ELSE
BEGIN
RETURN
END
No comments:
Post a Comment