How to run a stored procedure report and then send the result into email?

Question

Hi there

I am using SQL Server 2005. I have a report that is using stored procedure with just few lines of records and would like to send the whole recordset through an email and sets this into a schedule.

This is only an interm solution BTW till we integrate this stored procedure into reporting service + .net app.

I am appreciated your comment.

Thanks

Answer 1

If you don't already have Database Mail configured, you'll need to do so, including a profile that sends to a valid SMTP server. Once you do that, you can use sp_send_dbmail with the @query argument, which will embed the query results (e.g. "EXEC dbname.dbo.your_proc_name" - dbname is important) to an e-mail, or the @attach_query_result_as_file argument, which is self-explanatory.

sp_send_dbmail docs are here : http://msdn.microsoft.com/en-us/library/ms190307%28SQL.90%29.aspx