Email reports directly to the hands of your Executives

  • metawizard2

    Mr or Mrs. 500

    Points: 596

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

  • tony rogerson

    SSC Veteran

    Points: 242

    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

    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: 994648

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • jcrawf02

    SSC-Insane

    Points: 24198

    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

    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

    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: 994648

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Scott Arendt

    SSCertifiable

    Points: 7671

    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

    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

    Nice artice. Great solution.

  • Dallas Martin

    SSC Veteran

    Points: 231

    Wouldn't sp_makewebtask and sp_runwebtask also work for you?

    How about SQL Reporting Services?

  • Seth-284448

    Old Hand

    Points: 348

    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

    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

    Old Hand

    Points: 348

    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.

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

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