Sending table form HTML emails from a SQL Server database engine

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    Comments posted to this topic are about the item Sending table form HTML emails from a SQL Server database engine

  • Robert Sterbal

    SSChampion

    Points: 10995

    Evgeny Garaev - Sunday, January 28, 2018 10:20 PM

    Comments posted to this topic are about the item Sending table form HTML emails from a SQL Server database engine

    I would have liked to see more of the stored procedure inline with the article.

    412-977-3526 call/text

  • RandomStream

    Hall of Fame

    Points: 3735

    As the author implied, SSRS is an overkill for simple HTML reports like this. However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot. The setup proposed here is unnecessarily complex, requires its own set of tables and will require extra attention to make sure things run right. I appreciate the effort though. Thanks.

  • Robert Sterbal

    SSChampion

    Points: 10995

    RandomStream - Monday, January 29, 2018 10:25 AM

    As the author implied, SSRS is an overkill for simple HTML reports like this. However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot. The setup proposed here is unnecessarily complex, requires its own set of tables and will require extra attention to make sure things run right. I appreciate the effort though. Thanks.

    I think it makes logging easy, which is often critical for emailing

    412-977-3526 call/text

  • RandomStream

    Hall of Fame

    Points: 3735

    robert.sterbal 56890 - Monday, January 29, 2018 10:41 AM

    RandomStream - Monday, January 29, 2018 10:25 AM

    As the author implied, SSRS is an overkill for simple HTML reports like this. However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot. The setup proposed here is unnecessarily complex, requires its own set of tables and will require extra attention to make sure things run right. I appreciate the effort though. Thanks.

    I think it makes logging easy, which is often critical for emailing

    HTML report is a presentation layer. If you meant logging for the html report generation, sql server agent job history provides sufficient logging info.  I keep all performance data for up to 90 days in a dedicated database, just in case an old incident comes back to haunt me 😉

    I admit I'm a bit biased here as I have my own ways of doing things. Again I appreciate the author's effort in trying to find better ways to do things.

  • Robert Sterbal

    SSChampion

    Points: 10995

    I meant logging which emails got sent.

    412-977-3526 call/text

  • Sue_H

    SSC Guru

    Points: 90747

    robert.sterbal 56890 - Monday, January 29, 2018 12:10 PM

    I meant logging which emails got sent.

    Database Mail has logging.

    Sue

  • Robert Sterbal

    SSChampion

    Points: 10995

    I'm a fan off logging data where it is created, and not in a central store.

    I've often wondered how to get database mail compiled and sent, I've done this with a mailto: url in excel in the past. This is a nice solution for adhoc mailing projects.

    Do you have a favorite resource for learning about Database Mail?

    412-977-3526 call/text

  • Sue_H

    SSC Guru

    Points: 90747

    robert.sterbal 56890 - Monday, January 29, 2018 1:02 PM

    I'm a fan off logging data where it is created, and not in a central store.

    I've often wondered how to get database mail compiled and sent, I've done this with a mailto: url in excel in the past. This is a nice solution for adhoc mailing projects.

    Do you have a favorite resource for learning about Database Mail?

    It's not too complicated and much more stable then what has been used in SQL Server before. Here are some docs related to setting up and testing:
    Database Mail
    Database Mail Configuration
    How to configure database mail in SQL Server

    Sue

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    RandomStream - Monday, January 29, 2018 10:25 AM

    ...However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot...

    That make sense if you have 1-2 reports like that, but when you happened to have 10-20 of these it will be another story.
    With my solution for example managing recipient list is much easier and transparent.

  • RandomStream

    Hall of Fame

    Points: 3735

    Evgeny Garaev - Monday, January 29, 2018 1:23 PM

    RandomStream - Monday, January 29, 2018 10:25 AM

    ...However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot...

    That make sense if you have 1-2 reports like that, but when you happened to have 10-20 of these it will be another story.
    With my solution for example managing recipient list is much easier and transparent.

    If you have many reports to be generated for a mixed group of users, then SSRS subscription would be better.

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    robert.sterbal 56890 - Monday, January 29, 2018 9:04 AM

    I would have liked to see more of the stored procedure inline with the article.

    There are comments in the sql code attached to the article if you are interested. If you are afraid that it can harm your environment you can always deploy it on an isolated non-production server. Or just read the .sql file in Management Studio without executing it.

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    RandomStream - Monday, January 29, 2018 1:28 PM

    If you have many reports to be generated for a mixed group of users, then SSRS subscription would be better.

    There are some pros and cons for that. With my solution you don't need to have SSRS development experience. Also you don't need to deploy and configure SSRS which may be difficult in some cases.
    My solution picks up changes in metadata automatically with SSRS you need to re-design and re-deploy reports when the metadata has changed.
    By the way I have mentioned SSRS in my article 😉 I gave an alternative not a replacement for SSRS. Also there are many other alternatives like Tableau...

  • choodye

    Valued Member

    Points: 73

    robert.sterbal 56890 - Monday, January 29, 2018 10:41 AM

    RandomStream - Monday, January 29, 2018 10:25 AM

    As the author implied, SSRS is an overkill for simple HTML reports like this. However, if the intended tasks were simple, I prefer a self-contained XML method where I can easily tweak everything from one spot. The setup proposed here is unnecessarily complex, requires its own set of tables and will require extra attention to make sure things run right. I appreciate the effort though. Thanks.

    I think it makes logging easy, which is often critical for emailing

    I think that Evgeny did a great job with this post!  It is precise, to the point, and works as advertised.  It is not overly complex and worked for me right out of the gate and should be pretty easy to manage.
    Also, it beats the heck out of having to code each email with HTML formatting.
    Random Stream, perhaps you can write an article about the "self-contained XML method" that works just as well.
    As DBAs, we are always looking for better and more efficient ways to do things.

  • Robert Sterbal

    SSChampion

    Points: 10995

    Evgeny Garaev - Monday, January 29, 2018 2:55 PM

    robert.sterbal 56890 - Monday, January 29, 2018 9:04 AM

    I would have liked to see more of the stored procedure inline with the article.

    There are comments in the sql code attached to the article if you are interested. If you are afraid that it can harm your environment you can always deploy it on an isolated non-production server. Or just read the .sql file in Management Studio without executing it.

    Since the article was about sending mail, I thought the actual procedure that sends mail should be more than just an attachment.

    412-977-3526 call/text

Viewing 15 posts - 1 through 15 (of 16 total)

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