Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

html from sql server 2K8R2 Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 1:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:42 AM
Points: 102, Visits: 346
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,
Post #1385334
Posted Saturday, November 17, 2012 4:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 19, 2014 10:35 AM
Points: 12, Visits: 125
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'
' ;

Post #1386017
Posted Monday, November 19, 2012 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:42 AM
Points: 102, Visits: 346
Thank you for reply,
So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?

Thanks,
Post #1386449
Posted Monday, November 26, 2012 4:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 590, Visits: 2,755
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1388890
Posted Tuesday, November 27, 2012 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:42 AM
Points: 102, Visits: 346
Thanks, what if I need to change font-weight at <tr> level?
How do I access <tr>?
Post #1389365
Posted Tuesday, November 27, 2012 1:38 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 590, Visits: 2,755
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 ( (
SELECT td = 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...


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1389401
Posted Sunday, December 2, 2012 11:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:23 PM
Points: 65, Visits: 367
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.
Post #1391781
Posted Wednesday, December 5, 2012 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:42 AM
Points: 102, Visits: 346
Alan,
Sorry for delay, but better later than never.
Thank you.
That is what I was looking for.

Eugene
Post #1393221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse