tsql to export and attach SSRS report to email?

Question

I don't think there's an easy way to do this, but here goes. We have an `SSRS` report that can be invoked with `http://MyServer/ReportServer/Pages/ReportViewer.aspx?%2fSales%2fSalesReport` We also use database mail to send our emails, so the code looks like this: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Joe Smith', @recipients = '[email protected]', @subject = 'Daily Sales', @body = 'Some HTML', @body_format = 'HTML' ; Using only tsql, can I somehow include the Excel as an attachment to the email sent? The Excel can be viewed locally using `http://MyServer/ReportServer?%2fSaless%2fSalesReport&rs:Command=Excel`, but I'm not sure how I can include it as an attachment. Thanks.

Answer 1

Hi, this can be done using sp_send_dbmail from what I have worked on in the past and from what I have read online at

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017.

This is how I would approach it:

1. Call the Reporting Services web service with the appropriate parameter values (they are documented online). This call will return a Byte array (Byte()), which can then be saved to a file. Sounds as if you want to generate an Excel sheet as the output, so, this MIME type will have to be indicated as one of the input parameters to the web service call;
2. Once the byte array is saved to a file with the proper name and extension on the file system, insert a row into a database table indicating its full path on the file system. If possible, use an UNC path rather than one that contains a drive letter;
3. Assuming that (1) and (2) have been set up as scheduled tasks and have run successfully, you now have a physical file that can be sent as an attachment and its physical location in the database. One of the parameters to sp_send_dbmail is @file_attachments. Query the database table to get your attachment's full path, and then invoke sp_send_dbmail using the @file_attachments parameter.
4. Again, to recap: generate the byte array for the desired file as task (1); write the full path of this file to the database as task (2); and, finally, invoke sp_send_dbmail. My advice to you would be to encapsulate all three steps in a .NET command-line program, using the language of your choice, and, of course, implement any necessary error-handling logic. This command-line program can then be set to run at a predetermined interval using the Windows Task Scheduler. Good luck!