html from sql server 2K8R2

  • 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,

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

    ' ;

  • Thank you for reply,

    So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?

    Thanks,

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

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

    How do I access <tr>?

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

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

  • 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