November 15, 2012 at 1:18 pm
Hi all,
I am trying to generate an HTML-formated email from sql server. I need to be able to control the font-size/position of the table cell <td> or table row <tr>.
Here is the link of the sample I am trying to follow: http://grounding.co.za/blogs/romiko/archive/2008/09/21/generating-professional-email-notifications-with-sql.aspx
Could someone help?
IF (OBJECT_ID('#tmpDailyReport') IS NOT NULL)
DROP TABLE #tmpDailyReport
CREATE TABLE #tmpDailyReport(
Date varchar( 12 ) Null,
WebServerName varchar( 150 ) NOT Null,
Location varchar( 150 ) NOT Null,
cnt_RecsTotal int Null,
cnt_RecsFailed int Null
)
INSERT INTO #tmpDailyReport(
Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed
)
select '2008-01-01','Amsterdam-Server','Amsterdam',1000,2
union
select '2008-01-01','London-Server','London Bridge',50,0
union
select '2008-01-01','London-Server','London West Minster',80,0
union
select '2008-01-01','Portugal-Server','Lisbon',5,1
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H3>Daily Failure Report</H3>' +
N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), GETDATE(), 113) + '</FONT></H5></div>' +
N'<table border="1">' +
N'<tr><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' +
CAST ( (
SELECT td = Date, '', td = WebServerName, '', td = Location, '', td = cnt_RecsTotal, '',
'td/@bgcolor' = CASE WHEN cnt_RecsFailed > 0 THEN '#FF3366' END,
td = cnt_RecsFailed
FROM #tmpDailyReport
for xml path('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' +
N'
' ;
drop table #tmpDailyReport
Thanks,
November 17, 2012 at 4:38 pm
This seems to be more of an HTML/CSS formatting question than a SQL Server question, so perhaps try this:
1. Take the HTML created by the code you posted and output it with: select @tableHTML
2. Paste that output into Notepad and view it in a web browser.
3. Manually edit it by trial and error it until you get the desired result using references such as the "HTML Style Example - Font, Color and Size" example at w3schools.
4. Put the formatting want back into the T-SQL.
For example, this code makes the font of the red cells larger and they are sorted/ORDERed BY the number of failed records. (Is sorting what you meant by controlling the position of tr?)
SELECT td = Date, '', td = WebServerName, '', td = Location, '', td = cnt_RecsTotal, '',
'td/@style' = CASE WHEN cnt_RecsFailed > 0 THEN 'background-color:#FF3366; font-size:20pt;' END,
td = cnt_RecsFailed
FROM #tmpDailyReport
ORDER BY cnt_RecsFailed
for xml path('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' +
N'
' ;
November 19, 2012 at 9:14 am
Thank you for reply,
So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?
Thanks,
November 26, 2012 at 4:15 pm
eugene.pipko (11/19/2012)
Thank you for reply,So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?
Thanks,
Yes.
P.S. W3Schools is the best place to go for an HTML reference.
-- Itzik Ben-Gan 2001
November 27, 2012 at 12:07 pm
Thanks, what if I need to change font-weight at <tr> level?
How do I access <tr>?
November 27, 2012 at 1:38 pm
eugene.pipko (11/27/2012)
Thanks, what if I need to change font-weight at <tr> level?How do I access <tr>?
The top row (the first instance of the TR tag) you could add any attributes just like you would by writing the HTML like so:
N'<tr style=''font-family: arial black;''><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' +
There are a number of other ways... Let's say you wanted to access all the rows (<tr>'s), you could add a style sheet in the static HTML portion as follows:
SET @tableHTML =
N'<style> tr {font-family: Segoe Script;}</style>' +
N'<H3>Daily Failure Report</H3>' +
What is going on here is a mix of HTML and CSS; again, W3schools.com is the place to get a grasp of HTML, CSS, XML, etc..
Below is a sample that you can use as a reference. Notice how I change the first row (the 1st <tr>), all the rows (all <tr>'s) and all the cells (the <td>'s)
This:
IF (OBJECT_ID('tempdb..#tmpDailyReport') IS NOT NULL)
DROP TABLE #tmpDailyReport
CREATE TABLE #tmpDailyReport(
Date varchar( 12 ) Null,
WebServerName varchar( 150 ) NOT Null,
Location varchar( 150 ) NOT Null,
cnt_RecsTotal int Null,
cnt_RecsFailed int Null
)
INSERT INTO #tmpDailyReport(
Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed
)
select '2008-01-01','Amsterdam-Server','Amsterdam',1000,2
union
select '2008-01-01','London-Server','London Bridge',50,0
union
select '2008-01-01','London-Server','London West Minster',80,0
union
select '2008-01-01','Portugal-Server','Lisbon',5,1
--SELECT * FROM #tmpDailyReport
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<style> tr {font-family: Segoe Script;}</style>' +
N'<H3>Daily Failure Report</H3>' +
N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), GETDATE(), 113) + '</FONT></H5></div>' +
N'<table border="1">' +
N'<tr style=''font-family: arial black;''><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' +
CAST ( (
SELECTtd = Date, '',
td = WebServerName, '',
td = Location, '',
td = cnt_RecsTotal, '',
'td/@style' = CASE WHEN cnt_RecsFailed > 0 THEN 'font-weight: bold; color: pink;' END,
td = cnt_RecsFailed
FROM #tmpDailyReport
for xml path('tr'), TYPE) AS NVARCHAR(MAX) ) +
N'</table>' +
N'';
SELECT @tableHTML
drop table #tmpDailyReport
Will produce this:
<style> tr {font-family: Segoe Script;}</style><H3>Daily Failure Report</H3><div><H5>Execution Date: <font color="blue">27 Nov 2012
14:30:21:433</FONT></H5></div><table border="1"><tr style='font-family: arial
black;'><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th><tr><td>2008-01-
01</td><td>Amsterdam-Server</td><td>Amsterdam</td><td>1000</td><td style="font-weight: bold; color: pink;">2</td></tr><tr><td>2008-
01-01</td><td>London-Server</td><td>London Bridge</td><td>50</td><td>0</td></tr><tr><td>2008-01-01</td><td>London-
Server</td><td>London West Minster</td><td>80</td><td>0</td></tr><tr><td>2008-01-01</td><td>Portugal-
Server</td><td>Lisbon</td><td>5</td><td style="font-weight: bold; color: pink;">1</td></tr></table>
Edited: Code sample got jacked up...
-- Itzik Ben-Gan 2001
December 2, 2012 at 11:41 pm
I don't have a specific example to show you, but another approach is to use the SELECT command using the FOR XML switch. Then use XSLT transformations to convert to the target format. 😉
December 5, 2012 at 2:30 pm
Alan,
Sorry for delay, but better later than never.
Thank you.
That is what I was looking for.
Eugene
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply