Mike Dougherty-384281 (6/20/2013)
Are we solving the problem right?Are we solving the right problem?
Jeff asked why use xp_cmdshell to get to a third-party tool & suggested db_sendmail: depending on your environment, the answer to that first question is either "No" or "Right enough" 🙂
I wonder if composing html <table>s and emailing them is the right way to share content.
(aside from the pretty/ugly formatting of inline style workarounds for various capabilities of mail client(s) such as gmail stripping style tags and even inline attributes google decided aren't appropriate for your email or Outlook 2007/2010 using Word to render emails)
If the data is meant to be further consumed, the html table is somewhat klunky. If the data is meant to be looked at, there is too much requirement for 'pretty' to make emailed tables a robust solution. If the data is meant to be proof that something else happened, does it need to provide so much detail (and cost the overhead to get all that detail, formatted, etc.)?
If the data were staged for consumption by a data-query in Excel (for example) the analyst using it would be able to easily further transform via charting/pivoting/filtering, etc. If reporting services (either MS, or 3rd party such as Crystal Reports/Crystal Solutions) generates the 'pretty' version to a PDF - that could be mailed/attached/referenced. Another option might be to simply render the data to html via a web service: properly executed CSS can make a nice view for direct human eyes-on or the semantically correct table can be consumed by Excel's html table import (or googledocs html scraping equivalent)
Another consideration is the amount of content we _can_ get from a query may be much more than we _should_ put in an email. I know, nobody intends to put 30k rows of data in an email... When we built and tested there was only 200 rows in the table... 3 years later we're causing grief to the email administrator when we're storing an html version of the day's snapshot in 12 email recipient's inbox. (and 10 of those recipients keep everything we've ever mailed in "archive" folders on the email server) I'm not sure if this qualifies as Database Backup, but you can see how well it scales. 🙂
It really does depend. I can't answer for anyone else but I'm a mostly a data-troll and, as a result, don't know how to make a web service and will probably never learn how. I agree that such a thing or the use of Crystal Reports, SSRS, or any of a dozen other methods would probably be the best thing to do for "production runs" but simple embedded HTML created by a stored procedure works quickly and under my full control without much overhead for things like the morning reports that I have to send to people in IT.
I absolutely agree about the email thing where people keep stuff forever and that HTML formatting is worse than clunky if the data is to be consumed electronically. That's definitely something to consider before building such a repetative email regardless of method used to build it. Even something as simple as large TSV files (for import to Excel, for example) as attachments should be avoided in most cases because of such people. Far better to save it as a "managed" file somewhere that people can get to with their spreadsheets... no email required. Managed files also help with resource usage especially for such events as month end or daily reports using things like Crystal Reports or SSRS. It's far better to have one file somewhere that people can get to than to have hundreds of people all trying to run the same report. It kind of reminds me of the old "green bar" days where people would have a 40 pound bundle of green-bar paper reports delivered to their desk... they'd flip to page "x" and "y" to get a couple of numbers and then throw away the report. It was really silly and wasteful especially considering the number of people that it actually took to build the reports and deliver them.
--Jeff Moden
Change is inevitable... Change for the better is not.