Summary
This article outlines how to automate the delivery of reports via SQL queries.
Note: For assistance beyond what this article covers please contact Microsoft. Assistance by LogiSense here is not considered part of a standard support agreement. However, if required professional services can be arranged to implement these items
Configuration
In MSSQL Studio Management, click Management branch
Select Database Mail - configure this to send emails from your SQL server
Create a view that will include any filter criteria, sorting etc. To limit the size of the data being emailed, use 'Select top 20 *' or something similar so that you don't try to email thousands of rows of data
Create a new job with a step containing the below code
In the code below reference your new view with only 'select * from MYVIEW' (this is why all search / filter / sort criteria should be designed into your view as there are complications with quotes etc.)
Schedule the job to run as needed
Test by right clicking the new job and clicking execute
Example
See below a very basic example of attaching a set of results to an email
Send email functionality emailing the variable that contains the result of the query
USE msdb EXECUTE AS USER = 'dbo' EXEC msdb.dbo.sp_send_dbmail @recipients=N'mgallant@logisense.com', @body='See text attachment for billable users', @subject ='5xSunrise Billable Users', @profile_name ='Portal Check', @query ='SELECT * from boss.dbo.custombillablepackages', @attach_query_result_as_file = 1, @query_attachment_filename ='Results.txt'