How to Send a Scheduled HTML Report Directly from SQL Server
Write the query to retrieve the raw data – Use “AdventureWorks” database for the following example., Run the query and ship the results - SQL Server (since 2005) has a stored procedure to send emails directly from the database., Consider sending...
Step-by-Step Guide
-
Step 1: Write the query to retrieve the raw data – Use “AdventureWorks” database for the following example.
Try using information greater than the current date.
And these are the results. -
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 in the picture.
This is how it looks in Outlook. , If you need the results to have a more professional look, the best option is to send them as html.
That is a feature of the sdb.dbo.sp_send_dbmail stored procedure.
Combining that option and the XML capabilities of SQL Server.
Write a stored procedure to transform the results into an HTML table and returning characters.
Like the one in the picture.
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 the one on the picture.
This is what you will be getting. , This is how it will look at the end. , 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. -
Step 3: Consider sending them in HTML.
-
Step 4: Now you can add styling to the html you already have in the stored procedure in this way (see image).
-
Step 5: Schedule it and see it work - Since this a simple query it can be included in any SQL job.
Detailed Guide
Try using information greater than the current date.
And these are the results.
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 in the picture.
This is how it looks in Outlook. , If you need the results to have a more professional look, the best option is to send them as html.
That is a feature of the sdb.dbo.sp_send_dbmail stored procedure.
Combining that option and the XML capabilities of SQL Server.
Write a stored procedure to transform the results into an HTML table and returning characters.
Like the one in the picture.
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 the one on the picture.
This is what you will be getting. , This is how it will look at the end. , 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.
About the Author
Sarah Ramirez
Committed to making creative arts accessible and understandable for everyone.
Rate This Guide
How helpful was this guide? Click to rate: