Automated Report Delivery

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 code below
- 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'