August 20, 2008 at 5:10 pm
does anyone know how to color code the columns inside SQL 2005 HTML email?
For example: I have ColumnA and ColumnB.
inside columnA and columnB, it can hold a value of 0,1,2.
If the value in ColumnA is 0, the column should be RED. If it is 1, it should be GREEN. If it is 2, it should be GRAY.
Same goes for ColumnB. Does anyone know where I can get the information to make this happen?
Thanks.
/* post was also made in the admin section but I think it should belong here since it has to do w/ TSQL */
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
August 21, 2008 at 6:28 am
How are you getting the results into the email? Are you attaching query results? Please post your current code. My best guess is that you would have to add HTML tags to your query output.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2008 at 4:30 pm
this is what I got now to send out regular HTML email
set nocount on
declare @tableHTML nvarchar(max)
SET @tableHTML =
' ' +
' ' +
' ' +
' ' +
' ' +
CAST ( ( SELECT td = convert(varchar(20),dbname),'',
td = convert(varchar(20),srcSrv),'',
td = convert(varchar(20),destSrv),'',
td = cloneStatus,'',
td = dbStatus,''
FROM monitoring..cloneAuto where cloneStatus in (1,2) or dbStatus in (1,2)-- and jobstatus = 1
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
' ' +
'
' +
'Legend:
' +
'0 - failure
' +
'1 - successful
' +
'2 - job disabled' ;
--update monitoring..cloneAuto set cloneStatus = NULL, dbStatus = NULL where cloneStatus in (1,2) or dbStatus in (1,2)
raiserror('All Database Cloned Successfully',0,1) with nowait
exec msdb..sp_send_dbmail
@recipients = 'dba@domain.com'
, @subject = ' Cloning :: All Database Cloned Successfully'
, @body = @tableHTML
, @body_format = 'HTML'
set nocount off
for cloneStatus and dbStatus column, I want each column to change colors based on the value that's in the column. In regular HTML you would do:
but since this is TSQL, I can't really do that. There's a way to do it in C# but I'm not really good at C#.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
August 22, 2008 at 7:48 am
Sorry but the HTML is not visible. It's one of the nagging issues with the forum.
I'm not real proficient with HTML, but couldn't you do something like this on the columns you want colored?
td = Case When cloneStatus = 1 Then ' ' else Convert(char(1), cloneStatus) End,'',
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2008 at 11:50 am
hrmm using case statement, interesting idea.
I'll give it a shot and if it doesn't work, I'll just use Reporting Services.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
August 22, 2008 at 1:29 pm
nope, didn't work 🙁 . RS or C# here I come!
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
August 22, 2008 at 2:49 pm
I can tell you it is real easy in RS. At least the color coding part is. I'm not sure how you're running the email part.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2008 at 6:36 pm
RS is capable of sending out reports via email. I'm going to subscribe to the report and have it set to email me once a day 🙂
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply