December 10, 2015 at 9:44 am
Hello
I have a piece of code that looks at the history of a job and shows a step by step status
This is appearing as a table embedded within an e-mail
Is it possible to change the background colour to red if the run status returns FAILED?
At the moment the background colour is white for everything
Refer to:
'<td>' + cast(CASE
WHEN HIST.run_status = 0 THEN 'FAILED'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END as char(15)) + '</td>' +
Full Code:
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''
SET @subject = 'Status of last Load and Build Daily Data run'
SELECT @Table = @Table +'<tr style="background-color:#FFFFFF">' +
'<td>' + CAST(HIST.step_id AS CHAR(5)) + '</td>' +
'<td>' + CAST(HIST.step_name AS char(40)) + '</td>' +
'<td>' + cast(CASE
WHEN HIST.run_status = 0 THEN 'FAILED'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END as char(15)) + '</td>' +
'<td>' + cast(HIST.run_date as char(10)) + '</td>' +
'<td>' + STUFF(STUFF(RIGHT('000000' + CAST ( HIST.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') + '</td>' +
'<td>' + STUFF(STUFF(RIGHT('000000' + CAST ( HIST.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') + '</td>' +
'</tr>'
from sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
where JOB.name = 'Load and Build Daily Data'
and HIST.run_date = CONVERT(varchar(8), getdate(), 112)
SET @tableHTML =
N'<H3><font color="Black">Status of last Load and Build Daily Data run:</H3>' +
N'<table border="1" align="left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">
<th>Step ID</th>
<th>Step Name</th>
<th>Step Status</th>
<th>Run Date</th>
<th>Run Time</th>
<th>Run Duration</th>
</tr>' + @Table + N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients='bob@company.co.uk',
@profile_name = 'Bob',
@execute_query_database = 'msdb',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;
- Damian
December 10, 2015 at 10:30 am
You could use a CASE statement to set the color based on HIST.Run_status
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2015 at 4:18 am
Thanks, that seemed to work
Damian.
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply