html from sql server 2K8R2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » html from sql server 2K8R2

html from sql server 2K8R2

Rate Topic

Display Mode

Topic Options

Author

Message

rightontarget

Posted Thursday, November 15, 2012 1:18 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:49 PM
Points: 69, Visits: 220

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

George Rypysc

Posted Saturday, November 17, 2012 4:38 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 10:18 PM
Points: 12, Visits: 121

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

rightontarget

Posted Monday, November 19, 2012 9:14 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:49 PM
Points: 69, Visits: 220

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

Alan.B

Posted Monday, November 26, 2012 4:15 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 237, Visits: 1,195

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.

-- AJB
xmlsqlninja.com

Post #1388890

rightontarget

Posted Tuesday, November 27, 2012 12:07 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:49 PM
Points: 69, Visits: 220

Thanks, what if I need to change font-weight at <tr> level?
How do I access <tr>?

Post #1389365

Alan.B

Posted Tuesday, November 27, 2012 1:38 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 237, Visits: 1,195

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...

-- AJB
xmlsqlninja.com

Post #1389401

Brandon Forest

Posted Sunday, December 02, 2012 11:41 PM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:02 PM
Points: 60, Visits: 313

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. Wink

Post #1391781

rightontarget

Posted Wednesday, December 05, 2012 2:30 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:49 PM
Points: 69, Visits: 220

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 »

Permissions