Background
In my organization, the developers send a lot of database generated emails for informational purposes to ensure that everything in the system is still working 100%.We can call them production messages. Normally the way the email is presented is not important as it is only meant for the developer to help with system health check, but every now and again a business user would ask for the report (which was never meant to be an actual report) and then we would include the business user in the distribution list for this email.
Discussion
After we have included the business user in the mail group, it does not take very long for them to start complaining about how it is being presented and due to the huge backlog in our BI department, there is no real time to create a proper report in our reporting tools.
I started to think about the way we normally send these emails, you write your query and cast it to XML and pass the XML on the sp_send_dbmail procedure and define the body format as HTML. This was the key as most of us have worked with HTML sometime in our lives and I am not even close to being able to call myself an HTML expert, but I do know that you can define inline styling for HTML. As soon as this thought crossed my mind I went online to search for an HTML/CSS generator as I cannot remember how to generate CSS scripts. I found this great free HTML table generator website Tables Generator , this was exactly what I needed to format my tables in my emails. So I created a table on the generator to match our company branding and started to incorporate this into my emails. If I must say so myself I was really impressed with the end result and the business users stopped complaining about the ugly reports that they were not supposed to get in the first place.
I had to find a solution that would not take ages to create but looks good enough for the business to look at, and by using features/tools that are already at my exposure.
Considerations
This solution is in no way a replacement for proper reporting and BI, as it is very basic and static reports and I would not recommend using DBMail to extensively as it can possibly create extra overhead in your production environment. In saying this DBMail, uses service broker and should, in theory, have almost zero impact on your production environment but rather be safe than sorry.
Prerequisites
DB Mail needs to be setup, books online have great articles on how to achieve this.
AdventureWorks database is needed if you are going to follow the examples in this article.
Objective
We will be creating our result set in AdventureWorks and then create the script that will use to generate the CSS/HTML email. The end result should look something like the below screenshot of the table we are going to generate and include in our email.
Solution
First things first let’s create the SQL query that we will use to generate our required result set. In this case, we want to send a summary of the current week’s orders to ourselves and maybe even someone from the business. This could give a developer an indication of the systems performance and business a piece of mind.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @ReportingPeriodStart DATE; DECLARE @ReportingPeriodEnd DATE; -- Reporting date will from the beginning of the current week SET @ReportingPeriodStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())), 0); SET @ReportingPeriodEnd = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())) + 1, 0); PRINT @ReportingPeriodStart PRINT @ReportingPeriodEnd SELECT CONVERT(CHAR(8), OrderDate, 112) , SM.Name , COUNT(SalesOrderID) , CAST(SUM(TotalDue) AS NUMERIC(18, 2)) FROM [Sales].[SalesOrderHeader] SOH JOIN Purchasing.ShipMethod SM ON SM.ShipMethodID = SOH.ShipMethodID WHERE SOH.OrderDate >= @ReportingPeriodStart AND SOH.OrderDate < @ReportingPeriodEnd GROUP BY CONVERT(CHAR(8), OrderDate, 112) , SM.Name ORDER BY 1 , 2; |
I use two variables to get the start of the week and then end of the current week. We have to set the current date back 3 years to be able to get any results back from AdventureWorks. There is no need to use variables in the script you can place your date functions inside of the WHERE clause, but we could use this variable later on again for the subject of my email and maybe even to add a header to the body of the email. There is no need to alias the columns in the script as the HTML headers will handle this for us and when you convert the query to HTML you have to alias every column with ‘td’ and insert a blank column after every column defined in the query above.
Your next step would be to go and generate the HTML and CSS that you are going to use, or you could just use mine that I have already generated. I have generated 2 styles to be able to include 2 header rows in our email.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* Declare Variables for HTML */ DECLARE @Style NVARCHAR(MAX)= ''; /* Define CSS for html to use */ SET @Style += +N'<style type="text/css">' + N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}' + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}' + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#f38630;}' + N'.tg .tg-9ajh{font-weight:bold;background-color:#68cbd0}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}' + N'</style>'; |
Once we are happy with the inline style sheet, we can start with generating the HTML that will later be used in the sp_Send_DBMail.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
USE AdventureWorks2014; GO /* Declare Variables for DML */ DECLARE @ReportingPeriodStart DATE; DECLARE @ReportingPeriodEnd DATE; -- Reporting date will from the beginning of the current week SET @ReportingPeriodStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())), 0); SET @ReportingPeriodEnd = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())) + 1, 0); /* Declare Variables for HTML */ DECLARE @tableHTML NVARCHAR(MAX)= ''; SET @tableHTML += @Style + @tableHTML + N'<H2>Order Summary For : ' + CAST(@ReportingPeriodStart AS CHAR(10)) + ' to ' + CAST(@ReportingPeriodEnd AS CHAR(10)) + '</H2>' + N'<table class="tg">' --DEFINE TABLE /* Define Column Headers and Column Span for each Header Column */ + N'<tr>' + N'<th class="tg-hgcj" colspan="2">Order Information</th>' + N'<th class="tg-hgcj" colspan="2">Summary</th>' + N'</tr>' /* Define Column Sub-Headers */ + N'<tr>' + N'<td class="tg-9ajh">Order Date</td>' + N'<td class="tg-9ajh">Shipping Method</td>' + N'<td class="tg-9ajh">Order QTY</td>' + N'<td class="tg-9ajh">Order Total</td></tr>' /* Define data for table and cast to xml */ + CAST(( SELECT td = ISNULL(CONVERT(CHAR(8), OrderDate, 112),'2020-01-01') , '', td = ISNULL(SM.Name,'UNKNOWN') , '', td = ISNULL(COUNT(SalesOrderID),0) , '', td = ISNULL(CAST(SUM(TotalDue) AS NUMERIC(18,2)),0) , '' FROM [Sales].[SalesOrderHeader] SOH JOIN Purchasing.ShipMethod SM ON SM.ShipMethodID = SOH.ShipMethodID WHERE SOH.OrderDate >= @ReportingPeriodStart AND SOH.OrderDate < @ReportingPeriodEnd GROUP BY CONVERT(CHAR(8), OrderDate, 112) , SM.Name ORDER BY 1 , 2 FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>'; |
In the above script, we declare the variable where we are going to insert the HTML string for now. The HTML string starts off with an <H2> tag which allows us to give the table a header, this is very useful for when you want to add more than 1 table to the email.next step is to initiate the <table> tag and create the first header row and define how many columns this header row will span, in other words, we are using the first header row to group the second header rows into logical groupings. Once this is defined we can start to define the second header row. It is very important to remember that the columns you define in the headers match a number of columns that are going to be returned in your result set, excluding the blank columns. We will then have to add our query to the HTML string and cast it to XML with type PATH(‘tr’), this is to tell the HTML that each row in the result set is a table row in HTML. It is also a good idea to handle NULLS as this will shift your HTML table.
To test what we have created so far we can print the HTML string.
|
1 2 3 |
PRINT @tableHTML |
If everything goes according to plan SSMS should print the entire HTML string to the messages tab. We can copy this string and paste it into a new notepad and save this as .html file format, once this is done we can simply double-click the .html file and preview our results in a browser. I prefer using Notepad++ as my text editor it has an option to save a file as .html. I know you can do this in windows notepad as well if you select all file types and then just manually enter the file type as .html.
After you have saved the file you can go to the location of the file and double-click the .html file you just created, and you should see the following table in your browser.
Once we are happy with the output, we can move on to sending our first test mail using DBMail.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--DECLARE @fullFileName varchar(100) = 'E:\Reports\Orders\Archive\OrderDetail.xls' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile' , @recipients = '*****@outlook.com' , @from_address = '*****@outlook.com' , @body = @tableHTML , @body_format = 'HTML' , @subject = 'My Awesome Report' --, @file_attachments = @fullFileName; --YOU CAN ALSO ATTACH A FILE TO THE MAIL IF NEED BE. |
I have included but commented out the code for if you want to attach a file to an email. You will notice that we assign our HTML string variable to the @body parameter of sp_send_dbmail and set the @body_format parameter to ‘HTML’. And this is it to send beautiful emails from DBMail. Below is the code for the entire solution from start to finish.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
USE AdventureWorks2014; GO /* Declare Variables for DML */ DECLARE @ReportingPeriodStart DATE; DECLARE @ReportingPeriodEnd DATE; -- Reporting date will from the beginning of the current week SET @ReportingPeriodStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())), 0); SET @ReportingPeriodEnd = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(YEAR, -3, GETDATE())) + 1, 0); /* Declare Variables for HTML */ DECLARE @Style NVARCHAR(MAX)= ''; DECLARE @tableHTML NVARCHAR(MAX)= ''; /* Define CSS for html to use */ SET @Style += +N'<style type="text/css">' + N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}' + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}' + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#f38630;}' + N'.tg .tg-9ajh{font-weight:bold;background-color:#68cbd0}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}' + N'</style>'; SET @tableHTML += @Style + @tableHTML + N'<H2>Order Summary For : ' + CAST(@ReportingPeriodStart AS CHAR(10)) + ' to ' + CAST(@ReportingPeriodEnd AS CHAR(10)) + '</H2>' + N'<table class="tg">' --DEFINE TABLE /* Define Column Headers and Column Span for each Header Column */ + N'<tr>' + N'<th class="tg-hgcj" colspan="2">Order Information</th>' + N'<th class="tg-hgcj" colspan="2">Summary</th>' + N'</tr>' /* Define Column Sub-Headers */ + N'<tr>' + N'<td class="tg-9ajh">Order Date</td>' + N'<td class="tg-9ajh">Shipping Method</td>' + N'<td class="tg-9ajh">Order QTY</td>' + N'<td class="tg-9ajh">Order Total</td></tr>' /* Define data for table and cast to xml */ + CAST(( SELECT td = ISNULL(CONVERT(CHAR(8), OrderDate, 112),'2020-01-01') , '', td = ISNULL(SM.Name,'UNKNOWN') , '', td = ISNULL(COUNT(SalesOrderID),0) , '', td = ISNULL(CAST(SUM(TotalDue) AS NUMERIC(18,2)),0) , '' FROM [Sales].[SalesOrderHeader] SOH JOIN Purchasing.ShipMethod SM ON SM.ShipMethodID = SOH.ShipMethodID WHERE SOH.OrderDate >= @ReportingPeriodStart AND SOH.OrderDate < @ReportingPeriodEnd GROUP BY CONVERT(CHAR(8), OrderDate, 112) , SM.Name ORDER BY 1 , 2 FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>'; --DECLARE @fullFileName varchar(100) = 'E:\Reports\Orders\Archive\OrderDetail.xls' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile' , @recipients = '*****@outlook.com' , @from_address = '*****@outlook.com' , @body = @tableHTML , @body_format = 'HTML' , @subject = 'My Awesome Report' --, @file_attachments = @fullFileName; --YOU CAN ALSO ATTACH A FILE TO THE MAIL IF NEED BE. |
You will have to change the parameters of the sp_send_dbmail to match your environment’s settings. Once you have configured this you should receive an email that looks like this.
Final Thoughts
I like to create a stored procedure in a special database for every report mail I create, this allows for on central place to maintain all reports. Another way to manage the distribution lists for these emails is to create a lookup table for these report emails. By creating a lookup you will not have to change the stored procedure every time someone wants to be added or removed from this email, you can simply manage this lookup table’s data. I also know it is possible to use the google chart API to add charts to the email, I have also seen someone in my organization that creates the summary as per above and then create a additional .html file that is more interactive and attach it to the email for the users to play a bit with the summary data.
References
Jean-Pierre speaks at the Johannesburg SQL User Group, trying to give back to the SQL community as much as possible. He loves to tinker with SQL Server and see how he can approach a problem with a different angle.
View all posts by Jean-Pierre Voogt
- Running with running totals in SQL Server - July 29, 2016
- The new SQL Server 2016 sample database - July 22, 2016
- Storing Twitter feeds with Microsoft Flow in Azure SQL Database - June 29, 2016