1

Does anyone know how I can set 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

Ev.
  • 7,109
  • 14
  • 53
  • 87

1 Answers1

4

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.

Community
  • 1
  • 1
dawebber
  • 3,503
  • 1
  • 16
  • 16
  • Thanks a lot for the great response. I tried with the SSIS, but it proved to be a bit time consuming, so went with the DBMail. It works well, except it truncates my XML attachment at 2MB... any ideas on how to get around this? – Ev. Apr 28 '11 at 00:50
  • @Ev, I think I can help here. Follow [this thread] (http://stackoverflow.com/questions/997628/how-do-i-increase-the-maximum-allowed-attachment-size-for-email-sent-using-msdb-d) and you should be able to increase it. Keep in mind that some e-mail systems have a max attachment size limitations set by admins on the receiving side. Also, rare as it may be, some outgoing SMTP server control that size also. Should this limit be exceeded, your e-mails will not reach the recipient and will bounce. Just an FYI, not necessarily a problem in your case. – dawebber Apr 28 '11 at 02:48