SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email reports directly to the hands of your Executives


Email reports directly to the hands of your Executives

Author
Message
metawizard2
metawizard2
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 149
Comments posted to this topic are about the item Email reports directly to the hands of your Executives



tony rogerson
tony rogerson
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 76
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
metawizard2
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 149
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
Jeff Moden
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119290 Visits: 41482
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jcrawf02
jcrawf02
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2988 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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
metawizard2
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 149
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
metawizard2
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 149
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
Jeff Moden
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119290 Visits: 41482
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"... Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Scott Arendt
Scott Arendt
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4362 Visits: 1709
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
metawizard2
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 149
Its remarkable; once a user gets a taste of data on their berry, they are hooked. Wink 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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search