Email Formatted HTML Table with T-SQL

  • Very nice... You inspired me to write an all-in-one proc, based on your code, which can be called to output the results of any query (passed into it as an argument) as one of your HTML tables. I added CSS and support for a few more color schemes, and some logic to parse the input query.

    /*

    PROCEDURE [dbo].[proc_Output_As_HTML_Table_CSS_Formatted]

    Purpose: Outputs data from a query as an HTML table with CSS formatting.

    Examples:

    ----------------------------------------------------------------

    EXEC [proc_Output_As_HTML_Table_CSS_Formatted]

    'SELECT top 100 * FROM TestDB..TestHTMLTable' -- @SQL

    ,'LastName, FirstName' -- @OrderByClause

    ,'Top 100 randomly selected Mosaic employees...

    (This text is the @Msg argument that you passed in)'

    ,'Grey' -- @ColorSceme

    ,.90 -- @Size

    ,1 -- @ToFile = True (0=False; <>True)

    ,'\\TestServer\d$\SqlHtml.html' -- @FilePathWithName

    ----------------------------------------------------------------

    EXEC [proc_Output_As_HTML_Table_CSS_Formatted]

    -- @SQL (cannot contain a column named 'RowNumber')

    'SELECT

    [Record ID] = CAST(a.[RecordID] AS varchar(2))

    ,[Field Name] = a.[Field]

    ,[Entered Value A] = a.[Value]

    ,[Entered Value B] = b.ValueB

    ,[Entered Date] = CAST((CONVERT(smalldatetime, a.EnteredDate)) AS varchar(20))

    FROM

    TestDB..TestHTMLTableA a WITH (NOLOCK)

    INNER JOIN

    TestDB..TestHTMLTableB b WITH (NOLOCK)

    ON a.RecordID = b.RecordID

    WHERE (a.EnteredDate > GETDATE() - 1) '

    -- @OrderByClause (use lable names given above)

    ,'[Record ID]'

    -- @Msg (text added to HTML page above the table)

    ,'Below are record values stored in TestHTMLTableA compared to TestHTMLTableB'

    --

    ,'Dark Green' -- @ColorSceme

    ,1 -- @Size

    ,0 -- @ToFile = False (0=False; <>True)

    ,'' -- @FilePathWithName (@ToFile = False, so not needed)

    ----------------------------------------------------------------

    */

    ALTER PROCEDURE [dbo].[proc_Output_As_HTML_Table_CSS_Formatted]

    ---------------------------------

    ( @SQL varchar(8000)

    , @OrderByClause varchar(255)

    , @Msg varchar(1000)

    , @ColorSceme varchar(10)

    , @Size float

    , @ToFile bit

    , @FilePathWithName varchar(255)

    ) AS

    ---------------------------------

    DECLARE @SQLa varchar(8000)

    , @BorderColor varchar(8)

    , @HeaderColor varchar(8)

    , @HeaderFontC varchar(8)

    , @AltRowColor varchar(8)

    , @AltRowFontC varchar(8)

    ---------------------------------

    SET NOCOUNT ON;

    BEGIN TRY DROP TABLE ##base_data_table END TRY BEGIN CATCH END CATCH

    BEGIN TRY DROP TABLE ##html_output_table END TRY BEGIN CATCH END CATCH

    IF ISNULL(@Size,0) <= 0 SET @Size = 1

    SET @Msg = LTRIM(RTRIM(ISNULL(@Msg,'')))

    IF @Msg <> '' SET @Msg = @Msg + '

    '

    SET @ColorSceme = LTRIM(RTRIM(ISNULL(@ColorSceme,'')))

    ---------------------------------

    -- set color sceme

    ---------------------------------

    IF @ColorSceme = 'Blue' SELECT @BorderColor = '16365c', @HeaderColor = '1f497d', @HeaderFontC = 'fff', @AltRowColor = 'c5d9f1', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Red' SELECT @BorderColor = 'c00000', @HeaderColor = 'ff0909', @HeaderFontC = 'fff', @AltRowColor = 'ffcdcd', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Dark Red' SELECT @BorderColor = '632523', @HeaderColor = '963634', @HeaderFontC = 'fff', @AltRowColor = 'ddc1e3', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Purple' SELECT @BorderColor = '5a2d64', @HeaderColor = '874396', @HeaderFontC = 'fff', @AltRowColor = 'ccc0da', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Orange' SELECT @BorderColor = 'e26b0a', @HeaderColor = 'f79646', @HeaderFontC = 'fff', @AltRowColor = 'fde9d9', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Green' SELECT @BorderColor = '98bf21', @HeaderColor = 'A7C942', @HeaderFontC = 'fff', @AltRowColor = 'EAF2D3', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Dark Green' SELECT @BorderColor = '003300', @HeaderColor = '006c00', @HeaderFontC = 'fff', @AltRowColor = 'd8e4bc', @AltRowFontC = '000'

    ELSE IF @ColorSceme = 'Yellow' SELECT @BorderColor = '996600', @HeaderColor = 'f5d933', @HeaderFontC = '000', @AltRowColor = 'ffff99', @AltRowFontC = '000'

    ELSE /* @ColorSceme = 'Gray' */ SELECT @BorderColor = '595959', @HeaderColor = '7f7f7f', @HeaderFontC = 'fff', @AltRowColor = 'b8b8b8', @AltRowFontC = '000'

    ---------------------------------

    -- get base data, filter and sort it as expected for output, and number rows for use downstream

    ---------------------------------

    SELECT @SQLa = '

    SELECT ROW_NUMBER() OVER (ORDER BY ' +

    @OrderByClause

    -- Try to solve issue with dates and numbers sorting wrong... revisit this later

    -- CASE

    -- WHEN isnumeric(' + @OrderByClause + ') = 1 then Convert(float,' + @OrderByClause + ')

    -- WHEN isdate(' + @OrderByClause + ') = 1 then Convert(datetime,' + @OrderByClause + ')

    -- ELSE ' + @OrderByClause + '

    -- END

    + ') AS RowNumber, x.*

    INTO ##base_data_table

    FROM (' + @SQL + ' ) x'

    EXEC ( @SQLa )

    ---------------------------------

    -- string out the column names

    ---------------------------------

    DECLARE @colheads varchar(8000)

    , @datarows varchar(8000)

    ;WITH myColumns AS (

    SELECT TOP 100 PERCENT

    1 AS ID

    , '<td>'' + isnull(cast([' + CONVERT(varchar(300),c.NAME) + '] as varchar(8000)),'''') + ''</td>' AS data

    , '<th>' + CONVERT(varchar(300),c.NAME) +'</th>' AS head

    FROM

    tempdb.sys.columns AS c

    INNER JOIN

    tempdb.sys.types AS t

    ON c.user_type_id=t.user_type_id

    WHERE c.object_id = OBJECT_ID('tempdb..##base_data_table')

    AND CONVERT(varchar(300),c.NAME) <> 'RowNumber'

    ORDER BY c.column_id

    )

    SELECT DISTINCT

    @colheads = RTRIM(LTRIM(

    STUFF((SELECT x1.head

    FROM myColumns AS x1

    WHERE x1.ID = aa.ID

    FOR XML PATH(''),TYPE).value('.[1]','varchar(max)'),1,0,'')

    )),

    @datarows = RTRIM(LTRIM(

    STUFF((SELECT x2.data

    FROM myColumns AS x2

    WHERE x2.ID = aa.ID

    FOR XML PATH(''),TYPE).value('.[1]','varchar(max)'),1,0,'')

    ))

    FROM myColumns AS aa

    ---------------------------------

    --SELECT @colheads, @datarows

    -- 1. add html file and table header with css styling

    ---------------------------------

    SELECT SortOrder = 0,HtmlData = '

    <html>

    <head>

    <style>

    body {

    font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;

    font-size:' + CONVERT(varchar(10), (1.2 * @Size)) + 'em;

    }

    #t {

    width:100%;

    border-collapse:collapse;

    }

    #t td, #t th {

    font-size:' + CONVERT(varchar(10), (1 * @Size)) + 'em;

    border:1px solid #' + @BorderColor + ';

    padding:3px 7px 2px 7px;

    }

    #t th {

    font-size:' + CONVERT(varchar(10), (1.1 * @Size)) + 'em;

    text-align:left;

    font-weight: bold;

    padding-top:5px;

    padding-bottom:4px;

    background-color:#' + @HeaderColor + ';

    color:#' + @HeaderFontC + ';

    }

    #t tr.alt td {

    background-color:#' + @AltRowColor + ';

    color:#' + @AltRowFontC + ';

    }

    </style>

    <title></title>

    </head>

    <body><center>' + @Msg + '</center>

    <table id="t">

    <tr>

    ' + @colheads + '

    </tr>'

    INTO ##html_output_table

    ---------------------------------

    -- 2. add data formatted as html table rows (use modulo operator to alternate row colors)

    ---------------------------------

    SET @SQLa = '

    INSERT ##html_output_table

    SELECT SortOrder = RowNumber, HtmlData = ''

    <tr'' + CASE RowNumber % 2 WHEN 0 THEN '' class="alt"'' ELSE '''' END + ''>

    ' + @datarows + '

    </tr>''

    FROM ##base_data_table '

    EXEC ( @SQLa )

    ---------------------------------

    -- 3. add html file and table footer

    ---------------------------------

    INSERT ##html_output_table

    SELECT SortOrder = MAX(RowNumber)+1, HtmlData = '

    </table>

    </body>

    </html>'

    FROM ##base_data_table

    ---------------------------------

    -- strip white space

    ---------------------------------

    WHILE (SELECT MAX(CHARINDEX(' ', HtmlData)) FROM ##html_output_table) > 0

    UPDATE ##html_output_table SET HtmlData = REPLACE(HtmlData,' ',' ')

    WHILE (SELECT MAX(CHARINDEX('> ', HtmlData)) FROM ##html_output_table) > 0

    UPDATE ##html_output_table SET HtmlData = REPLACE(HtmlData,'> ','>')

    WHILE (SELECT MAX(CHARINDEX(' <', HtmlData)) FROM ##html_output_table) > 0

    UPDATE ##html_output_table SET HtmlData = REPLACE(HtmlData,' <','<')

    WHILE (SELECT MAX(CHARINDEX(char(13), HtmlData)) FROM ##html_output_table) > 0

    UPDATE ##html_output_table SET HtmlData = REPLACE(HtmlData,char(13),'')

    WHILE (SELECT MAX(CHARINDEX(char(10), HtmlData)) FROM ##html_output_table) > 0

    UPDATE ##html_output_table SET HtmlData = REPLACE(HtmlData,char(10),'')

    ---------------------------------

    -- create output

    ---------------------------------

    IF @ToFile <> 0

    BEGIN

    DECLARE @cmd varchar(4000)

    SET @cmd = 'BCP '

    SET @cmd = @cmd + '"select HtmlData from ##html_output_table order by SortOrder" '

    SET @cmd = @cmd + 'queryout "' + @FilePathWithName + '" -T -c -t '

    SET @cmd = @cmd + '-S ' + @@SERVERNAME

    EXEC MASTER..xp_cmdshell @cmd, NO_OUTPUT

    END

    ELSE

    SELECT HtmlData FROM ##html_output_table ORDER BY SortOrder

    ---------------------------------

    -- clean up

    ---------------------------------

    BEGIN TRY DROP TABLE ##base_data_table END TRY BEGIN CATCH END CATCH

    BEGIN TRY DROP TABLE ##html_output_table END TRY BEGIN CATCH END CATCH

    ---------------------------------

  • Your Google charts solution is very, very nice... koodos to you for that. 🙂

  • How is it possible that you are a newbie to SSC dpepper?! The truth is that I wrote the article, knowing it was rather simplistic, because I couldn't find a solution after over an hour of searching, at least not a solution that did not involve SSRS. Then you put one out that even has color schemes - freakin' nice!

    I think the charting example and your proc are certainly article material. It probably takes 30-40 minutes to write up an article. I know personally that I got much more out of the discussion, including a few merited slaps on the hand for not using sp_send_dbmail, than the time I put into it.

  • steve.ledridge (6/20/2013)


    I found the timing of this to be interesting as I just completed a first draft of a CLR Function the returns a SQL table as HTML Table code in order to create better formatted reports.

    Heh... Nicely done... and people wonder why I say that I miss sp_MakeWebTask. You could pass it nearly any reasonable query and it would "make the pretty stuff". You could even use a style sheet with it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you, I have not posted too much but I have been doing this for years and have quite an arsenal of code snippets I would love to share. I tend to focus on those projects that someone has told me "you can't do that in SQL" just to prove it can be done. I am also a big recycler of yours and other developers code since I hate re-inventing something from scratch when there is a better solution already started or completed by another SQL Architect. In fact a major portion of the CLR sproc was based on the code at http://sqlclrexport.codeplex.com/[/url]. this code was VB and I needed to convert it to C# for my purposes and I only grabbed the HTML portion since I already had other code for the other outputs I was exporting.

    I will try to challenge myself to post here more often as I am sure my code will inspire better ideas that will come back to benefit us all.

  • steve.ledridge (6/21/2013)


    Thank you, I have not posted too much but I have been doing this for years and have quite an arsenal of code snippets I would love to share. I tend to focus on those projects that someone has told me "you can't do that in SQL" just to prove it can be done. I am also a big recycler of yours and other developers code since I hate re-inventing something from scratch when there is a better solution already started or completed by another SQL Architect. In fact a major portion of the CLR sproc was based on the code at http://sqlclrexport.codeplex.com/[/url]. this code was VB and I needed to convert it to C# for my purposes and I only grabbed the HTML portion since I already had other code for the other outputs I was exporting.

    I will try to challenge myself to post here more often as I am sure my code will inspire better ideas that will come back to benefit us all.

    I look forward to seeing you around more. You sound like a "kindred spirit" in the world of T-SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Is there any reason for this approach IF SSRS is installed? I'm thinking a simple query, report and subscription would be the way to go.

  • heb1014 (6/24/2013)


    Is there any reason for this approach IF SSRS is installed? I'm thinking a simple query, report and subscription would be the way to go.

    That's how I would knock this out. No digging around with T-SQL building HTML manually.

    The only zinger would be the google charts. I haven't used those but SSRS has lots of built-in charts and there are truckloads of third-party options too, free or paid.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I have a question.

    I'm doing something similar, but have an additional requirement that I haven't been able to solve maybe you can help.

    Using your example, I need to add another row above the data grid that only has 3 cells but must span the entire grid.

    - The first cell would need to be the align with your #1 cell, but have no text but a red background.

    - The second cell would need to span your cells 2 and 3 with white text on a blue background.

    - The third cell would need to span your cells 4 and 5 with white text on a black background.

    Does anyone have a way of adding this?

    Any help would be appreciated!

  • To: mr3316a;

    This is a very simple solution to just your problem exactly as you have defined it, so this would apply to only you case (everyone else ignore this).

    Modify the existing procedure something like below, presuming your input @SQL will only ever produce 5 columns, you could insert an additional header row containing inline styling and static text values, insert it directly below '<table id="t">' like so:

    ' <table id="t">

    <tr>

    <th style="background-color: red"></th>

    <th colspan="2" style="background-color: blue"><font color="#ffffff">Static Text Cols 2 and 3</font></th>

    <th colspan="2" style="background-color: black"><font color="#ffffff">Static Text Cols 4 and 5</font></th>

    </tr>

    <tr>

    ' + @colheads + '

    </tr>'

    Insert a non-breaking-space into the first column ... this message editor translates that out 😉

  • Dave Vroman (6/20/2013)


    I didn't do that portion of the requirement. I only got the results. I wrote their encryption package and their encryption interface fully compatible with classic ASP, VB.NET, ASP.NET and VB6 and SQL. I was in charge of the DTS / SSIS packages and needed results from them and ran into several roadblocks. The compliance requirements were especially stringent because the web site got hacked with all of the resulting hassles.

    I no longer work there and I don't deal with PCI any more. Thank Goodness. BTW: The PCI which we were required to do was because of a law suit and the court was much more restrictive.

  • That was helpful, but I'm still having an issue depending on the email system the is being used (see attachment).

    When viewing in Outlook the report displays correctly, but when viewed in Yahoo Mail or on the Blackberry some borders and color tags are being stripped out.

    Here is the code I'm using, any suggestions?

    DECLARE @bodyMsg NVARCHAR(MAX)

    DECLARE @subject NVARCHAR(MAX)

    DECLARE @tableHTML NVARCHAR(MAX)

    DECLARE @EmailProfile NVARCHAR(MAX)

    DECLARE @EmailTitle NVARCHAR(MAX)

    DECLARE @EmailDateRange NVARCHAR(MAX)

    DECLARE @ReportMonth NVARCHAR(MAX)

    DECLARE @MtdTitle NVARCHAR(MAX)

    DECLARE @DailyTitle NVARCHAR(MAX)

    DECLARE @recipientsMain NVARCHAR(MAX)

    DECLARE @recipientsMain1 NVARCHAR(255)

    DECLARE @recipientsMain2 NVARCHAR(255)

    DECLARE @recipientsCC NVARCHAR(MAX)

    DECLARE @recipientsCC1 NVARCHAR(255)

    DECLARE @recipientsCC2 NVARCHAR(255)

    DECLARE @recipientsBCC NVARCHAR(MAX)

    DECLARE @recipientsBCC1 NVARCHAR(255)

    SET @EmailProfile = (SELECT TOP 1 EMAIL_PROFILE_NAME

    FROM TMP_ONTIME_SUMMARY);

    SET @EmailDateRange = (SELECT TOP 1 REPORT_DATE_RANGE

    FROM TMP_ONTIME_SUMMARY);

    SET @recipientsMain = ''

    SET @recipientsMain1 = (SELECT TOP 1 EMAIL_RECIPIENT_MAIN_1

    FROM TMP_ONTIME_SUMMARY);

    SET @recipientsMain2 = (SELECT TOP 1 EMAIL_RECIPIENT_MAIN_2

    FROM TMP_ONTIME_SUMMARY);

    IF LEN(@recipientsMain1) > 0

    SET @recipientsMain = RTRIM(@recipientsMain1)

    IF LEN(@recipientsMain2) > 0

    SET @recipientsMain = @recipientsMain + ';' + RTRIM(@recipientsMain2)

    SET @recipientsCC = ''

    SET @recipientsCC1 = (SELECT TOP 1 EMAIL_RECIPIENT_CC_1

    FROM TMP_ONTIME_SUMMARY);

    SET @recipientsCC2 = (SELECT TOP 1 EMAIL_RECIPIENT_CC_2

    FROM TMP_ONTIME_SUMMARY);

    IF LEN(@recipientsCC1) > 0

    SET @recipientsCC = RTRIM(@recipientsCC1)

    IF LEN(@recipientsCC2) > 0

    SET @recipientsCC = @recipientsCC + ';' + RTRIM(@recipientsCC2)

    SET @recipientsBCC = ''

    SET @recipientsBCC1 = (SELECT TOP 1 EMAIL_RECIPIENT_BCC

    FROM TMP_ONTIME_SUMMARY);

    IF LEN(@recipientsBCC1) > 0

    SET @recipientsBCC = RTRIM(@recipientsBCC1)

    SET @ReportMonth = (SELECT TOP 1 REPORT_MONTH

    FROM TMP_ONTIME_SUMMARY);

    SET @EmailTitle = (SELECT TOP 1 EMAIL_SUBJECT

    FROM TMP_ONTIME_SUMMARY);

    SET @MtdTitle = 'MTD: ' + RTRIM(@ReportMonth)

    SET @DailyTitle = 'Daily: ' + RTRIM(@EmailDateRange)

    SET @tableHTML =

    N'<style type="text/css">

    #outsideborder-table

    {

    align: left;

    border-collapse: collapse;

    border-top: 7px solid #000000;

    border-bottom: 7px solid #000000;

    border-left: 7px solid #000000;

    border-right: 7px solid #000000;

    }

    #box-table

    {

    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;

    font-size: 12px;

    text-align: center;

    border-collapse: collapse;

    border-top: 7px solid #9baff1;

    border-bottom: 7px solid #9baff1;

    }

    #box-table td

    {

    border-right: 1px solid #aabcfe;

    border-left: 1px solid #aabcfe;

    border-bottom: 1px solid #aabcfe;

    color: #000000;

    }

    </style>'+

    --Outside Border and style

    N'<table id="outsideborder-table" >'+

    --Head Line style format

    N'<style type="text/css">

    table

    {

    border-collapse:collapse;

    font-family:Arial;

    font-size:small;

    }

    h2

    {

    color: #fe0202;

    font-family:Arial

    }

    h3

    {

    color:#000000;

    font-family:Arial

    }

    </style>'+

    --Head Line On-Time Service % (Static)

    N'<H2 align="center" >On-Time Service %</H2>' +

    --Head Line Report Period (Static)

    N'<H2 align="center" >Report Period</H2>' +

    N'<table id="box-table" >

    <tr>

    <style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #fe0202;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th></th>

    <style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #00f;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th colspan = "4">' + @DailyTitle + '</th>

    <style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #000000;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th colspan = "4">' + @MtdTitle + '</th>

    </tr>

    <tr>'+

    N'<style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #fe0202;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th>SDC Markets</th>

    <style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #00f;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th>All Up</th>

    <th>SDC</th>

    <th>PUD</th>

    <th>Except Cnt</th>

    <style type="text/css">

    table th

    {

    font-size: 13px;

    font-weight: normal;

    background: #000000;

    border-right: 2px solid #9baff1;

    border-left: 2px solid #9baff1;

    border-bottom: 2px solid #9baff1;

    color: #fff;

    }

    </style>

    <th>All Up</th>

    <th>SDC</th>

    <th>PUD</th>

    <th>Except Cnt</th>

    </tr>' +

    CAST( (SELECT

    "td/@align" = 'left', td=SDCLocation ,'',

    "td/@align" = 'right', td=DAllUp ,'',

    "td/@align" = 'right', td=DSDC ,'',

    "td/@align" = 'right', td=DPUD ,'',

    "td/@align" = 'center', td=DExcptCnt ,'',

    "td/@align" = 'right', td=MAllUp ,'',

    "td/@align" = 'right', td=MSDC ,'',

    "td/@align" = 'right', td=MPUD ,'',

    "td/@align" = 'center', td=MExcptCnt ,''

    FROM (SELECT Top(100)MARKET_NM AS SDCLocation,

    DAILY_ALL_PCT AS DAllUp,

    DAILY_SDC_PCT AS DSDC,

    DAILY_PUD_PCT AS DPUD,

    DAILY_EXCEPTION_DAY_CNT AS DExcptCnt,

    MONTH_ALL_PCT AS MAllUp,

    MONTH_SDC_PCT AS MSDC,

    MONTH_PUD_PCT AS MPUD,

    MONTH_EXCEPTION_DAY_CNT AS MExcptCnt

    FROM TMP_ONTIME_SUMMARY

    ORDER BY SEQUENCE_NUM

    ) A FOR XML PATH('tr'), TYPE )

    AS NVARCHAR(MAX)

    ) +

    N'</box-table >'+

    N'</outsideborder-table>'

    --Email data

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = @EmailProfile,

    @recipients = @recipientsMain,

    @copy_recipients = @recipientsCC,

    @blind_copy_recipients = @recipientsBCC,

    @subject = @EmailTitle,

    @body = @tableHTML,

    @body_format = 'HTML' ;

  • Is there a way to set your column width to fixed positions so that one is not taking most of the space?

  • Nice article. I have used the script and created the HTM email but I have noticed that the results where truncated at some length and instead of getting 761 rows in the table I got about half of them and in the last row first cell it was cut. All variable where declared as in the original script.

    Any idea?

  • Excellent work man, thanks a lot!

Viewing 15 posts - 31 through 45 (of 83 total)

You must be logged in to reply to this topic. Login to reply