July 21, 2008 at 5:08 pm
How to send a query resultset via email in the message body section not as an attachment?
I have a simple query to run and need to send the result via email (in the message body).
How to do it? Either SQL2000 or SQL2005 will be fine.
Note: I post this question here as I am not sure where to post. It is SQL related but not version related.
July 21, 2008 at 6:05 pm
I think you'd need to put the result set into one column, then insert your other into the same column, select out as one big message, and then add as the email.
I used to do something like this, two column table, (date + email), insert data as desired (meaning in date/time order needed) into the table. I forget how I selected this out, but it worked and came as an email.
July 21, 2008 at 9:36 pm
Do your query normal query. Assign your result set to the appropriate variable, perhaps @msg varchar(2000).
Send results
EXEC master.. sp_send_dbmail
...
...
@body = @msg
July 21, 2008 at 10:49 pm
I recommend reviewing the help in BOL for sp_send_dbmail for additional parameters.
From BOL:
[ @query = ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
And, a few paragraphs further down:
[ @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.
In fact, there are quite a few parameters available that are specific to including a query in email. Again, please lookup the help in BOL for additional information.
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
July 21, 2008 at 10:59 pm
I recommend not using @query. Do your query first. Then simply assign the results to a variable and then assign that variable to @body.
July 21, 2008 at 11:05 pm
David (7/21/2008)
I recommend not using @query. Do your query first. Then simply assign the results to a variable and then assign that variable to @body.
Is there a particular reason why you suggest that? Not saying it is wrong - but wondering why you would recommend against using the query features.
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
July 22, 2008 at 7:54 am
Thank you all for your input. I have more options to try now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply