Basic reporting and email with SQL 2005?

  • Hi - i'm an experienced Sybase/Oracle/UNIX DBA but relative SQL 2005 newbie. I was wondering if someone could provide some direction on how I can do some basic reporting and send the output via email:

    The "short" story is I'm looking for a way to combine the results of several queries and send that output via email. The basic SQL queries are easy – I’m just not sure how to combine their output into one report and send it via email. In the Unix world I would use shell scripts and the equivalent of sqlcmd to capture the output and then use an OS command to send that output via email. Can someone tell me how I should approach this in Windows and SQL 2005?

    The longer story is I've created a very simple table that tracks database names and sizes on a daily basis. Our environment is somewaht volatile, with databases being created/deleted relatively frequently. I want to produce a report on a weekly basis that shows the number of databases in the instance for the current week as compared to the prior week. Plus a separate section for their sizes. As I said, the SQL to accomplish this is easy - I'm just not sure how to capture that output and send it via email. I know I can use sp_send_dbmail, but it appears it only accepts one variable as the "body" of the message - and I'm not sure how to get the results of several queries into one variable. My apologies if this is a stupid question, but can someone point me in the right direction?

    TIA!

  • sp_send_dbmail has an @query parameter. This parameter will attach the output of the specificed query into the body of the message.

  • You can go through your results and convert them to nvarchar (building an html or text file in an nvarchar(max)) and passing that to sp_send_dbmail.

    Or create a vbscript (or any script language in windows) to pull out the data, reformat, and email using smtp.

    Or send a separate email for each query using sp_send_dbmail

    Or find a third party tool to purchase that does what you need (SSRS, Crystal Server, or something more simple)

  • Other option could be a powershell script - sqlcmd and send the email. It really depends upon what you are doing. For anything that needs to be accessible or sent to end users I would recommend implementing Reporting Services.

    For simple admin type notifications, sp_send_dbmail or powershell scripts.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

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