Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Email reports directly to the hands of your Executives Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2008 10:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 7:37 PM
Points: 58, Visits: 149
Comments posted to this topic are about the item Email reports directly to the hands of your Executives


Post #523016
Posted Tuesday, June 24, 2008 10:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:37 PM
Points: 13, Visits: 58
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
Post #523017
Posted Wednesday, June 25, 2008 5:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 7:37 PM
Points: 58, 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



Post #523167
Posted Wednesday, June 25, 2008 5:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 36,013, Visits: 30,300
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523177
Posted Wednesday, June 25, 2008 5:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 2,628, Visits: 19,098
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."
Post #523186
Posted Wednesday, June 25, 2008 5:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 7:37 PM
Points: 58, 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



Post #523187
Posted Wednesday, June 25, 2008 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 7:37 PM
Points: 58, 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



Post #523200
Posted Wednesday, June 25, 2008 6:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 36,013, Visits: 30,300
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


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523209
Posted Wednesday, June 25, 2008 6:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 3,367, Visits: 1,448
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
Post #523227
Posted Wednesday, June 25, 2008 6:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 7:37 PM
Points: 58, Visits: 149
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.


Post #523228
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse