SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Austin Zellner / Email reports directly to the hands of your Executives / Latest Posts

RE: Email reports directly to the hands of your Executives

sheldonzola — Thu, 26 Jun 2008 09:18:58 GMT

You can also use msdb.dbo.sp_send_dbmail to embed the resultset directly into the body of the email:EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Server Agent Mail Profile' ,@recipients = @to_list ,@copy_recipients = @cc_list ,@body = @msg ,@query = 'SELECT source, emplid, fullName AS name, visaTypeFSA as fsaVisa, visaType as psVisa, reason FROM fsaAtlasReporting.dbo.tblDataLinkIDsReport' ,@subject = @subject ,@attach_query_result_as_file = 0 ,@query_result_header = 1

RE: Email reports directly to the hands of your Executives

Seth-284448 — Thu, 26 Jun 2008 08:48:21 GMT

I didn't realize that the generated email was text. I was confused by the html table displayed under "Pretty" Output in the article. Nice job on the technique. We are on sql 2000, so I will look into this solution.Thanks,Seth

RE: Email reports directly to the hands of your Executives

Peter Schott — Wed, 25 Jun 2008 21:08:16 GMT

Quick note - good solution to sending report-formatted data in SQL 2000. With the number of 2000 installs out there, this helps.Agreed on using DB Mail to send HTML Formatted e-mail and it's relatively easy to drop data into a table. I prefer Reporting Services' Data-driven subscriptions, but in a pinch DB Mail will work.And I also agree that execs tend to prefer something pushed to them on a regular basis over anything they have to actively retrieve, no matter how easy it is to retrieve it. It's another step to go through to get the data. Well summarized data in an e-mail is easy to digest and determine if they need more details.

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 18:12:28 GMT

I'm not familiary with sp_makewebtask; what does it do?

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 18:09:37 GMT

I'm not familiar with Reporting Services, so I can't comment on the merits; however, if you are using it successfully then I would be interested in your experience.One aspect of this solution is that it is designed to fit the small screen real estate of the target device ( specifically the blackberry, but could be tweaked for any ). If you are dealing with devices with larger screen real estate, you could increase the columns, or cut the number of columns for smaller screen devices ( older style verizon phones, text messaging, pagers, etc. )

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 18:05:43 GMT

Seth,take a look at the second post in the string; there is a great link to an article that shows how to create an HTML email, and it would be relatively easy to integrate in with the solution in this article.Thanks!Austin

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 18:04:37 GMT

Seth,I'm not sure about Blackberry's support for HTML ( maybe someone else could comment ).For this specific solution, the email is built only of text, so no HTML tags are needed. The centering function makes sure that everything lines up inside the text. Therefore it will work with any email client regardless of HTML support.Austin

RE: Email reports directly to the hands of your Executives

timothyawiseman — Wed, 25 Jun 2008 14:09:32 GMT

Its a very nice article. As several people have already pointed out, it is not the only possible solution, but it looks like a good one. It is similar to something I use currently, though my executives prefer excel on the laptop so that is what they receive.

RE: Email reports directly to the hands of your Executives

Seth-284448 — Wed, 25 Jun 2008 09:40:41 GMT

Can someone comment on my question regarding html tags? Does bbry not require these?

RE: Email reports directly to the hands of your Executives

vyankarla-586955 — Wed, 25 Jun 2008 09:35:21 GMT

How about using SQL reporting services for this purpose. I use reporting services to email patient census to my management team which is scheduled to deliver at specified intervals.

RE: Email reports directly to the hands of your Executives

Cosmic Charlie — Wed, 25 Jun 2008 09:31:37 GMT

I suppose I should have qualified my statement with "an environment running multiple mailservers, content filters, and sql server instances."The solution presented will not work for me, we do not have Outlook installed on the servers with sql server installed. xp_sendmail has always given us headaches when it comes to mass mailings, which could apply here, but in most cases will not.Sorry for jumping the gun, I get a little passionate about my work.

RE: Email reports directly to the hands of your Executives

Steve Jones - SSC Editor — Wed, 25 Jun 2008 09:05:48 GMT

I think this is a great idea, and it's similar to one I've used to email myself DBA reports every day.xp_sendmail has worked fine for me for years. And very solid. There are bugs and it can be a problem overall, but in many environments it works great.alternatively, in SS2K5 and above, use Database Mail instead for sending. In SS2K, xp_smtpmail is also a third party add-in I'd recommend, but there's nothing wrong with using xp_sendmail if it works well in your environment.

RE: Email reports directly to the hands of your Executives

Seth-284448 — Wed, 25 Jun 2008 08:32:43 GMT

I don't think Database.Mail is available in SQL 2000.

RE: Email reports directly to the hands of your Executives

Seth-284448 — Wed, 25 Jun 2008 08:31:32 GMT

We actually use something called sp_send_cdosysmail. We found it on this site and it works well for us. I also found it on Microsoft's website, so I don't know who is the original author. I think it might only send text email though.I can't speak to other people's experiences, but from my own, using CDO from this procedure has worked well for me.

RE: Email reports directly to the hands of your Executives

Cosmic Charlie — Wed, 25 Jun 2008 08:22:43 GMT

No offense, but xp_sendmail is a horrible solution! xp_sendmail uses cdo, which is buggy and prone to crashing without giving any error messages.:crazy:Go to http://msdn.microsoft.com/en-us/library/ms189505.aspx, and Microsoft tells you:[b]"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To send mail from SQL Server, use Database Mail."[/b]

RE: Email reports directly to the hands of your Executives

Seth-284448 — Wed, 25 Jun 2008 07:41:21 GMT

Austin,This looks like a great solution. Quick question: I don't see any html tags (html, body, etc.).How does the bbry know how to interpret the data? I'm not familiar with the bbry email client, so that may just be the way bbry's handle it. I know that my windows mobile device doesn't accept html emails. That seems to be one benefit of bbry over windows mobile devices.This may be asking for too much, but is there a way to send the email with both html type formatting and also text formatting, so that if the device doesn't support html (like my device), it would show the text version? I know that your solution was aimed at getting away from the tedious task of formatting text.Seth

RE: Email reports directly to the hands of your Executives

Dallas Martin — Wed, 25 Jun 2008 07:40:42 GMT

Wouldn't sp_makewebtask and sp_runwebtask also work for you?How about SQL Reporting Services?

RE: Email reports directly to the hands of your Executives

Anipaul — Wed, 25 Jun 2008 06:43:43 GMT

Nice artice. Great solution.

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 06:37:51 GMT

Its remarkable; once a user gets a taste of data on their berry, they are hooked. ;) Its really a unit of work to reward issue; to get to online reports, you have to turn on your laptop, dial in / login, goto the report. For the email reports, open an email on the berry ( which is most likely being checked often anyway ), and you are there.

RE: Email reports directly to the hands of your Executives

Scott Arendt — Wed, 25 Jun 2008 06:37:27 GMT

My personal experience is that no matter how easy you make it to get to the data, executives, users, customers, etc will never find it more convenient than the report that is e-mailed directly to them. Scott

RE: Email reports directly to the hands of your Executives

Jeff Moden — Wed, 25 Jun 2008 06:24:50 GMT

Umm... I don't email reports or even let folks know when something has been updated... because it's always updated (with a few exceptions of course).Favorite "trick" is to write an Excel spreadsheet that grabs external data from a view I've written. Data refreshes when they open the spreadsheet. They don't need to see this type of stuff on the "crack-berry"... :P

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 06:06:51 GMT

Jon,I think it is good to have consistency in where your users go to get their data. In my case, pushing the data to the execs was an add on to the systems that they can already get to; the data that was sent was a "cliff notes" version of what they could get online, and the idea is to give them the ability to see key metrics without having to dial in to a system ( important in a situation where they travel a lot / not system friendly / what have you ).I am also curious: how are others training their users, and what kind of results are you getting?Austin

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 05:52:11 GMT

Jeff,Thanks for the feedback. I had meant for the title to be a "grabber", but I honestly had taken the ability to send email ( have it setup and ready to go ) for granted. There may be another article here on the configuration of SQL server to be able to send mail; I've always had it set to use the administrator account, which would be setup on the server with an email profile ( perhaps not the best way ). It would be interesting to set it up to have different email profiles, possibly a different one for each kind of email going out ( marketing, support, etc. ).Austin

RE: Email reports directly to the hands of your Executives

jcrawf02 — Wed, 25 Jun 2008 05:49:34 GMT

[quote][b]metawizard2 (6/25/2008)[/b][hr]An opportunity in your solution is that it would be possible to add links to the data, allowing users to follow the links to see the detail. This could generate another email that would give them more information.[/quote]Good article, but I'm wondering if it's really necessary to push the data to the executives at all? The approach I'm trying to get going in my own workspace is that we give executives and middle management set links in their departmental pages that point to reports, then we generate xml or html output that is overwritten daily/weekly/whatever. User always goes to the same place to get their data, and can access it whenever they wish. Ideally, we then refresh in the middle of the night, so it's always updated when they come in the next day. I guess if they like the feeling that it's being hand-delivered, you could send a standard email with the link once the refresh runs. Version control, you say? Well, just include datename(something,getdate()) in the filename and dynamically build the URL when the link is clicked to take you to the current version. Add in control buttons to move next and previous, and you're good to go. Curious whether others think emailing directly is advantageous over my suggested approach, and why? Thanks!Jon

RE: Email reports directly to the hands of your Executives

Jeff Moden — Wed, 25 Jun 2008 05:35:11 GMT

Nice article, Austin... title is just a bit deceiving, though. Most of the article talks about how to setup a report instead of about how to email. From what I understand, there is some good bit of setup before you can use xp_sendmail and I would have liked to see that in a article.Other than that, very thorough and well laid out! Thanks!

RE: Email reports directly to the hands of your Executives

metawizard2 — Wed, 25 Jun 2008 05:20:37 GMT

Tony,Great link! This would make a great addition. That also answers a very long standing question I've had; specifically, how to send emails as HTML from SQL server: @body_format = 'HTML'.From an inclusion / application standpoint, your script could be added and then compare against a value about the recipient indicating if they prefer HMTL or Text email.An opportunity in your solution is that it would be possible to add links to the data, allowing users to follow the links to see the detail. This could generate another email that would give them more information.Thanks,Austin

RE: Email reports directly to the hands of your Executives

tony rogerson — Tue, 24 Jun 2008 22:51:06 GMT

Also check out my blog article: [url=http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/03/28/send-table-or-view-as-embedded-html-lt-table-gt-in-an-email-stored-procedure.aspx[/url] which given a table or view will convert it into html and email it.Tony Rogerson, SQL Server MVP[url=http://sqlblogcasts.com/blogs/tonyrogerson]http://sqlblogcasts.com/blogs/tonyrogerson[/url][url=http://sqlknowhow.com]http://sqlknowhow.com[/url] -- Real world training

Email reports directly to the hands of your Executives

metawizard2 — Tue, 24 Jun 2008 22:48:44 GMT

Comments posted to this topic are about the item [B]Email reports directly to the hands of your Executives[/B]