Automatically Email query Results in SQL Server 2005

Does anyone know how I can s开发者_如何转开发et up a job in SQL Server 2005 that will email the results of a query as an attachment?

Preferably I'd like to email them as XML so they can be opened nicely in Excel, but I'll settle for a CSV...

Thanks in advance!

-Ev

Basically, you can use DBMail, if that's your preferred method of sending e-mail from within SQL server. Here's another thread that shows you an example and talking about limitation of sending query results via that method.

So, add this to your scheduled job and you should get desired results.

Here's how to control the query output and placement in the e-mail straight from MSDN if you want to use CSV or similar:

[ @attach_query_result_as_file= ] attach_query_result_as_file Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename= ] query_attachment_filename Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header= ] query_result_header Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

[ @query_result_width = ] query_result_width Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

[ @query_result_separator= ] 'query_result_separator' Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

If you want to use XML, you just have to make sure your query returns XML. Otherwise, you'd have to write a procedure to format tabular query as XML.

Here's another route using SSIS, but it's more involved, but gives you more control over the output and formatting.