Step 2. Run the query and ship the results
SQL Server (since 2005) has a stored procedure to send emails directly from the database. SQL Mail must be configured and running in the server for this stored procedure to work.
Using msdb.dbo.sp_send_dbmail you can send the results of a query as an email as simple as follows:
And this is how it looks in Outlook:
It’s that simple.
Step 3. Behold!
Obviously those results need to look nice if your customer’s clients (and their clients) are willing to receive it.
In order to turn this report into a something more professional looking the best option is sending that as html. That’s another nice feature of the msdb.dbo.sp_send_dbmail stored procedure. Combining that option and the XML capabilities of SQL Server, I wrote a stored procedure to transform the results into an HTML table and returning characters.
This is the stored procedure:
The result of the previous stored procedure is the raw html report. Take a look at the “for xml path(‘tr’)” part of the query, which is simply creating the tr-td tags structure of the table.
Run the report like this:
This is what I got now:
Even better: add styling to the html you already have in the stored procedure in this way:
This is how it looks in the end:
Step 4. Schedule it and see it work
Since this a simple query it can be included in any SQL job. It’s not difficult to get a list of recipients with their email addresses and create a cycle to execute the msdb.dbo.sp_send_dbmail procedure within the same job.
Finally, schedule the job to run as frequently as necessary and get a daily report directly from SQL.