Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a query that I would like to run against SQL Server on a regular schedule and have the results of the query emailed to a distribution list. I'm not looking for anything fancy, just plain text with the query text would be enough for my purposes.

Is there anything that would do that out of the box, or do I need to write an app to do that?

share|improve this question

2 Answers 2

You can easily do this using the SQL Server DB Mail (SQL 2005+, I think). You'll need to enable it via the Surface Area Configuration tool (or system options) and configure mail profiles on your SQL Server instance prior to using it.

You can look at SQL BOL, or this MSDN article which lists all of the DB Mail stored procedures and provides additional links for using them.

Once you have DB Mail enabled and have configured a mail profile, the syntax to email query results would be something like this:

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'me@mydomain.com',
    @body='Query Results Are Attached', 
    @subject ='Query Results',
    @profile_name ='MyDBMailProfile',
    @query ='SELECT * FROM mytable', 
    @attach_query_result_as_file = 1,
    @query_attachment_filename ='QueryResults.txt'

After you have your statement working properly you can create a SQL Agent job to execute it and have it run on a regular schedule.

share|improve this answer

Alternatively you can use osql to run the query from a batch file run from the Task Scheduler. The advantage of this is that your batch file can run the query to generate the output file then post process it to do whatever tidying up is appropriate. My own preference is not to use osql, but to do everything from a VBScript script as it makes it easy to generate a pretty report.

squillman's solution is quicker though.

JR

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.