Email Formatted HTML Table with T-SQL

Question

Email Formatted HTML Table with T-SQL

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

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

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 1

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

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

GoofyGuy SSCertifiable Points: 6109 More actions · Answer 7

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

August 31, 2015 at 2:49 pm

#1823482

The comments are really helpful for this article.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 9

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 !

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

ckret007 SSC Rookie Points: 44 More actions · Answer 11

Would anyone happen to know how to add another table?

Feedback would be greatly appreciated.

Thanks.

TJDBA Default port Points: 1490 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

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)

wongsc2 SSC Journeyman Points: 83 More actions · Answer 14

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!

richard.reay Valued Member Points: 71 More actions · Answer 15

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