Email Formatted HTML Table with T-SQL

  • Yes, I know. I actually edited the article to say the same thing but it needs to be republished. You can can get rid of step 3 by doing this:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'MYSERVER Email',

    @recipients = 'foo@foo.com',

    @subject = 'The Email Subject',

    @query =N'EXECUTE MYDB.dbo.procHtmlSql',

    @attach_query_result_as_file = 0,

    @body_format = 'HTML';

  • This is a nice article that generates lots of interesting discussions and topics.

    But from pure technical perspective, I feel there is one special deficiency I do not like, that is: hard-coding of column names and lack of flexibility to handle multiple queries.

    I recently submitted an article to mssqltips.com, and in that article, I addressed this deficiency with the help of PowerShell and .Net. The article is accepted and should appear soon.

  • jeffrey yao (8/28/2015)


    This is a nice article that generates lots of interesting discussions and topics.

    But from pure technical perspective, I feel there is one special deficiency I do not like, that is: hard-coding of column names and lack of flexibility to handle multiple queries.

    I recently submitted an article to mssqltips.com, and in that article, I addressed this deficiency with the help of PowerShell and .Net. The article is accepted and should appear soon.

    Heh... I think it's a deficiency to have to use PowerShell and .Net to do such a simple thing. 😉 I also agree that it would be nice to have something generic that has dynamic column names and table sources but the purpose of this article wasn't to provide a total solution. It was meant to demonstrate how to easily generate HTML for a given table output. From there, you can certainly let your imagination lose.

    Looking forward to your good article, Jeffrey. Thanks for the heads up.

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

  • Why are we still emailing tables of data?

    Can't we have whatever triggering event send a link for a full-featured data explorer to our preferred communication provider (email/sms/tweet/etc)?

    I mean... it's 2015 already. 🙂

  • Mike Dougherty-384281 (8/28/2015)


    Why are we still emailing tables of data?

    Can't we have whatever triggering event send a link for a full-featured data explorer to our preferred communication provider (email/sms/tweet/etc)?

    I mean... it's 2015 already. 🙂

    I can't speak for anyone else but formatting a table for output is just an example in most cases. I don't send or receive data by email. Typically, we use FTP, SFTP, or some other vehicle for the transfer of data.

    It IS, however, a very useful technique for me as a DBA. I create an output (results of overnight jobs, for example) that I have the server send to me that 1) flags if there's a problem in the title so that I know whether it urgently needs to be opened or not 2) provides all the troubleshooting information that I need if there is a problem and 3) provides a log that auditors are very happy with because it's not living on the server where I could modify 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

  • Jeff Moden (8/28/2015)


    Mike Dougherty-384281 (8/28/2015)


    Why are we still emailing tables of data?

    Can't we have whatever triggering event send a link for a full-featured data explorer to our preferred communication provider (email/sms/tweet/etc)?

    I mean... it's 2015 already. 🙂

    I can't speak for anyone else but formatting a table for output is just an example in most cases. I don't send or receive data by email. Typically, we use FTP, SFTP, or some other vehicle for the transfer of data.

    It IS, however, a very useful technique for me as a DBA. I create an output (results of overnight jobs, for example) that I have the server send to me that 1) flags if there's a problem in the title so that I know whether it urgently needs to be opened or not 2) provides all the troubleshooting information that I need if there is a problem and 3) provides a log that auditors are very happy with because it's not living on the server where I could modify it.

    It is a tremendously useful technique. I have a number of jobs that trap errors, continue on and then send me a list of problems that have occurred during the run. If, for example, one of the DBAs create a database and don't take a full backup, the log backup will fail. Instead of bombing and not continuing, the procedure checks for the problem up front and builds a list for me, which it sends me when the work is done. A TRY...CATCH block catches anything I haven't anticipated. The email it sends me includes a table of what's wrong. It saves me time every morning and I don't think I'd want to be without this technique.

    Another example is a major web site I wrote. Our employees administer some portions of the site through administration pages. They can change the configuration for clients. They have made mistakes from time to time, so I wrote a procedure to check for problems. I'm not talking about invalid data here, as that is prevented by design. I mean that they create a situation where some specific situation isn't covered any more. When a problem is found, it sends them an email so they know to login and fix something.

  • I hate to be negative about someone's efforts, but I think this solution is 'the long way around the barn'.

    XSLT, despite the author's comment to the contrary, is not terribly difficult, even in simple use cases, and it would elegantly incorporate the CSS styling elements lacking in the author's approach.

    The author's solution uses xp_cmdshell, which I don't believe to be a security risk, but which some DBAs do. That could be an impediment to developing this kind of solution in some shops.

    Finally, using a free SMTP client seems needlessly redundant, when SQL Server already has the Send_DBMail SSP.

  • The comments are really helpful for this article.

  • jeffrey yao (8/28/2015)


    This is a nice article that generates lots of interesting discussions and topics.

    But from pure technical perspective, I feel there is one special deficiency I do not like, that is: hard-coding of column names and lack of flexibility to handle multiple queries.

    I recently submitted an article to mssqltips.com, and in that article, I addressed this deficiency with the help of PowerShell and .Net. The article is accepted and should appear soon.

    In case, you want to take another approach to this "HTML formatted table report", here it is 😛

    https://www.mssqltips.com/sqlservertip/4032/generate-html-formatted-emails-from-sql-server/

    Cheers !

  • jeffrey yao (9/24/2015)


    jeffrey yao (8/28/2015)


    This is a nice article that generates lots of interesting discussions and topics.

    But from pure technical perspective, I feel there is one special deficiency I do not like, that is: hard-coding of column names and lack of flexibility to handle multiple queries.

    I recently submitted an article to mssqltips.com, and in that article, I addressed this deficiency with the help of PowerShell and .Net. The article is accepted and should appear soon.

    In case, you want to take another approach to this "HTML formatted table report", here it is 😛

    https://www.mssqltips.com/sqlservertip/4032/generate-html-formatted-emails-from-sql-server/

    Cheers !

    I guess my question would be, since it's just as complicated as doing it in T-SQL, why would you want to use the extra complication and addition to the proverbial "Tower of Babel" by using PowerShell?

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

  • Would anyone happen to know how to add another table?

    Feedback would be greatly appreciated.

    Thanks.

  • Thankyou so much for the code snippet.

    But I have a problem, that I have EMPTY(NULL) columns.

    because of this the columns in the email get shifted to the left.

    Please help to resolve this

  • tauseef.jan (3/15/2016)


    Thankyou so much for the code snippet.

    But I have a problem, that I have EMPTY(NULL) columns.

    because of this the columns in the email get shifted to the left.

    Please help to resolve this

    Most likely, you'll need to replace the "empty" cells with non-breaking spaces (I forget the HTML entitization code for that but should be easy for you to find).

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

  • hey this is very useful! thank you so much. but what if i need to output more than 1 query?

    if i do 2 sets of HTML body, the second table (for 2nd query) is floating inconsistently. i need it to be side by side in outlook. please help! thank you!

  • With regard to the HTML table of results how would you align data in a specific column? For example, I want data in column 2 (Description) to be left aligned with all other columns of data remaining as it is.

    Thanks

Viewing 15 posts - 61 through 75 (of 83 total)

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