March 26, 2009 at 12:39 pm
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!
March 26, 2009 at 12:42 pm
sp_send_dbmail has an @query parameter. This parameter will attach the output of the specificed query into the body of the message.
March 26, 2009 at 12:55 pm
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)
March 26, 2009 at 1:16 pm
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