Schedule a daily SQL query and email results as Excel file

Question

I need to schedule a report to run at 2:00 am on weekdays and have those query results emailed to end users as an Excel file. I typically query a data warehouse using Excel's Power Query for other reports. To my knowledge this particular functionality does not exist within the DW tool I use (Jet Data Manager) or Power Query. Is this problem solvable with SSMS? Thanks

Answer 1

Doing so with pure SQL will be challenging, but maybe not impossible. The most straight-forward way of doing this type of thing would be to use SSRS (SQL Server Reporting Services). This would allow you to build formatted reports as well as setup scheduling, distribution (to different formats including Excel), etc... Implementation of SSRS might be overkill for one report, but if you have other, similar requirements, it could make your life a lot easier.

A pure SQL approach would probably involve the following parts:

1. A SQL Agent job could provide the scheduling, etc...
2. SQL Server also has a feature called database mail - this is typically configured for sending alerts, but you could also use it to send the results of a query
3. The tricky part is your excel requirement. sp_send_dbmail (the proc you would use to send the mail) does allow you to attach query results to the message. But, you might have to live with something like CSV (that could be opened in Excel).

There could be some other things I'm not thinking of, but hopefully this will help.