sp_send_mail HTML formatting issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_send_mail HTML formatting issue

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-03-26 : 14:38:51

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';

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 15:15:43

In this line, there shouldn't be </th> tag at the end
N'<table border="1"></th>' +

In this line, the <TH> after Server should be a </TH>
N'<TR><TH>Server<TH><TH>Status<TH></TR>'+

The tags should really be all lower case, to conform to the w3c standard.

As to making the background color dynamic, use something like this. Replace:


N'<TR bgcolor="#A9F5F2"><TD>ONE</TD>'+

With:


N'<TR bgcolor="' + CASE
	WHEN @status1 = 'success' AND @status2 = 'success' THEN '#00FF00'
	ELSE '#FF0000'
	END
 +  '"><TD>ONE</TD>'+

There are 10 types of people in the world, those that understand binary, and those that don't.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-03-29 : 09:23:32

Thank You. Im going to give this a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources