March 26, 2010 at 12:41 pm
Not too sure, If this forum fits here but here it goes.
I send out the following email everyday.What I would like to do is to format the bg color of the row based on the value.If value of @status1 & @status2 is 'success' make row green else red.How can I achive this ?
Here is my code, can this be done and how ?
declare @status1 varchar(100),@status2 varchar(100),@one int , @two int, @one1 varchar(10), @two1 varchar(10)
set @one = ( Select Exist from dbo.Daily_Results where [Server]='ONE')
set @two = ( Select Exist from dbo.Daily_Results where [Server]='TWO')
set @one1 = ( Select [File_Name] from dbo.Daily_Results where [Server]='ONE')
set @two1 = ( Select [File_Name] from dbo.Daily_Results where [Server]='TWO')
if @one =0
set @status1 = 'Unsuccessful,' + @one1
else
set @status1 = 'Successfully ,' + @one1
if @two =0
set @status2 = 'Unsuccessful,' + @two1
else
set @status2 = 'Successfully,' + @two1
declare @sub varchar(200)
set @sub = 'Daily Backup Status'
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H3>Daily Status</H3>' +
N'<table border="1"></th>' +
N'<TR><TH>Server<TH><TH>Status<TH></TR>'+
N'<TR bgcolor="#A9F5F2"><TD>ONE</TD>'+
CAST((SELECT
td = @status1, ''
FOR XML PATH('td'), TYPE) AS NVARCHAR(MAX)) + N'</TR>
<TR bgcolor="#A9F5F2"><TD>TWO</TD>'+
CAST((SELECT
td = @status2, ''
FOR XML PATH('td'), TYPE) AS NVARCHAR(MAX))+ N'</TR>
</table>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='XXXX',
@subject = @sub,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'Daily Status';
March 26, 2010 at 1:27 pm
Based on your scipt I would declare a seperate variable that would be included in your if statements. If the status is Success set the variable value to 'Green' (or the hex value for the color) then when you write your output insert the variable in place of the bgcolor for the row.
This may be over simplyfing your need but based on the quick clance at your script I would think that would work.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 26, 2010 at 1:43 pm
could you give me an example. Thank You
March 26, 2010 at 1:58 pm
It might look something like this
if @one =0
set @status1 = 'Unsuccessful,' + @one1
set @color1 = 'RED'
else
set @status1 = 'Successfully ,' + @one1
set @color1 = 'GREEN'
if @two =0
set @status2 = 'Unsuccessful,' + @two1
set @color2 = 'RED'
else
set @status2 = 'Successfully,' + @two1
set @color2 = 'GREEN'
declare @sub varchar(200)
set @sub = 'Daily Backup Status'
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H3>Daily Status</H3>' +
N'<table border="1"></th>' +
N'<TR><TH>Server<TH><TH>Status<TH></TR>'+
N'<TR bgcolor="'+(select @color1)+'"<TD>ONE</TD>'+
CAST((SELECT
td = @status1, ''
FOR XML PATH('td'), TYPE) AS NVARCHAR(MAX)) + N'</TR>
<TR bgcolor="'+(select @color2)+'"><TD>TWO</TD>'+
CAST((SELECT
td = @status2, ''
FOR XML PATH('td'), TYPE) AS NVARCHAR(MAX))+ N'</TR>
</table>'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 26, 2010 at 2:32 pm
Thank You Dan. I would give it a try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply