Email reports directly to the hands of your Executives

Question

Email reports directly to the hands of your Executives

metawizard2

Mr or Mrs. 500

Points: 596
More actions
June 24, 2008 at 10:48 pm

#193724

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

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

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

tony rogerson SSC Veteran Points: 242 More actions · Answer 1

Also check out my blog article: 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 which given a table or view will convert it into html and email it.

Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

http://sqlknowhow.com -- Real world training

metawizard2 Mr or Mrs. 500 Points: 596 More actions · Answer 2

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

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

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!

--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)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 4

metawizard2 (6/25/2008)
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.

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

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

metawizard2 Mr or Mrs. 500 Points: 596 More actions · Answer 5

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

metawizard2 Mr or Mrs. 500 Points: 596 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

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"... 😛

--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)

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 8

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

metawizard2 Mr or Mrs. 500 Points: 596 More actions · Answer 9

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.

Anipaul SSC-Insane Points: 24681 More actions · Answer 10

Anipaul

SSC-Insane

Points: 24681

June 25, 2008 at 6:43 am

#833873

Nice artice. Great solution.

Dallas Martin SSC Veteran Points: 231 More actions · Answer 11

Wouldn't sp_makewebtask and sp_runwebtask also work for you?

How about SQL Reporting Services?

Seth-284448 SSC-Addicted Points: 458 More actions · Answer 12

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

Cosmic Charlie Old Hand Points: 315 More actions · Answer 13

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:

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

Seth-284448 SSC-Addicted Points: 458 More actions · Answer 14

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.