Sending table form HTML emails from a SQL Server database engine

Question

Sending table form HTML emails from a SQL Server database engine

Evgeny Garaev

SSCertifiable

Points: 7428
More actions
January 28, 2018 at 10:20 pm

#326623

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

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

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

Robert Sterbal SSChampion Points: 11052 More actions · Answer 1

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 More actions · Answer 2

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: 11052 More actions · Answer 3

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 More actions · Answer 4

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: 11052 More actions · Answer 5

I meant logging which emails got sent.

412-977-3526 call/text

Sue_H SSC Guru Points: 90860 More actions · Answer 6

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: 11052 More actions · Answer 7

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: 90860 More actions · Answer 8

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: 7428 More actions · Answer 9

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 More actions · Answer 10

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: 7428 More actions · Answer 11

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: 7428 More actions · Answer 12

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 More actions · Answer 13

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: 11052 More actions · Answer 14

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