SQL Server has some handy features: one of those features is dbmail, which I tend to use less until I’ve been asked to create a reports to be distributed on a daily basis. These reports can’t use any external tools (such as Reporting Services) and no attachments are allowed. The process of creating and timing a report is pretty straightforward: write a T-SQL query, create SQL Server Agent job and schedule it, and then use dbmail to send the output by email. Some adjustments can be made to the email, such as sending the query result as an attachment (@attach_query_result_as_file), setting the importance of the email (can be significant in case of alert) and setting no-reply (@reply_to).

Even though there’s much to say about dbmail, we will focus on the query generating the content of the email. Mostly, the result set of this query will be small, a few to tens of rows. However, unless it’s limited with the TOP clause or an equivalent, there’s no guarantee about the number of rows in the result set. This might cause of headaches, and we’ll see how.

The target table of all queries is [Sales].[SalesOrderHeader] in the [AdventureWorks2012] database. The table has 31,465 rows and is nearly 5MB.

So let’s say we want to send a report with the result set of the following query:

SELECT
[SalesOrderId]
, [OrderDate]
, [CustomerId]
, [TotalDue]
FROM 
[AdventureWorks2012].[Sales].[SalesOrderHeader]
ORDER BY
[SalesOrderId];
GO

Executing it with IO and TIME statistics on, my machine came up with the following result:

(31465 row(s) affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 686, physical reads 3, read-ahead reads 682, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 1407 ms.

Please note, I’ll be using the SELECT @body statement to get the result set, so the returned value will not be the actual variable content. To get the actual content of the @body variable, you can use dbmail, C#, etc.

Usually, we’ll need to do some casting to varchar, which might be time consuming in finding the appropriate casting (in this example the money data type is converted to string and is trimmed on the left). I executed the following query:

DECLARE @body varchar(MAX) = '';
SELECT
@body = @body + ''
+ '' + CAST( [SalesOrderId] AS nvarchar)
+ '' + CONVERT( nvarchar, [OrderDate], 121)
+ '' + CAST( [CustomerId] AS varchar)
+ '' + CAST( [TotalDue] AS varchar)
+ ''
FROM 
[AdventureWorks2012].[Sales].[SalesOrderHeader]
ORDER BY
[SalesOrderId];
SET @body = '' + @body + '
Sales Order ID
Order Date
Customer ID
Total Due
';
SELECT @body;
GO

I ran the query and added a TOP clause, using different TOP values:

Wow! The TOP 10000 executed for almost 19 minutes. Growing 10 times from execution to execution didn’t give any indication for the last execution. Even though all executions share the same execution plan, the last execution requires a worktable. Alas, this might have impact on resources, locks, etc.

However, we can use the FOR XML PATH to get the same result in less time and without casting the data:

DECLARE @body nvarchar(MAX) = '';
SET @body = 
''
+ CAST( (
SELECT
[SalesOrderId] AS 
, [OrderDate] AS 
, [CustomerId] AS 
, [TotalDue] AS 
FROM 
[AdventureWorks2012].[Sales].[SalesOrderHeader]
ORDER BY
[SalesOrderId]
FOR XML RAW('tr'), ELEMENTS
) AS NVARCHAR(MAX))
+ N'
Sales Order ID
Order Date
Customer ID
Total Due
';
SELECT @body;
GO

The statistics are much better, returning all rows in less than 1 second.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'SalesOrderHeader'. Scan count 1, logical reads 686, physical reads 3, read-ahead reads 682…
Table 'Worktable'. Scan count 0, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 18579, lob physical reads 4, lob read-ahead reads 6336.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 553 ms.

Using the FOR XML PATH did the casting to string on the result set and not on each column separately. We used the RAW to add the new table row string at the beginning of each record. The ELEMENTS and the column name helped us set the cells tags in the HTML table; otherwise, each tag would contain the column name.

As you can see, using FOR XML PATH can improve the way we write our reports. Not only did we improve the performance of the report, we eased the pressure on resources. Another benefit is knowing that the report can handle large data volume in case the result set is bigger than expected. And last but not least, using the FOR XML PATH relieves us from the casting operations, thus saving time writing code and providing data close to its actual format.

Once the report’s query is done, we can use sp_send_dbmail to send the report:

DECLARE @body nvarchar(MAX) = '';
SET @body = 
''
+ ''
+ CAST( (
SELECT TOP 10
[SalesOrderId] AS 
, [OrderDate] AS 
, [CustomerId] AS 
, [TotalDue] AS 
FROM 
[AdventureWorks2012].[Sales].[SalesOrderHeader]
ORDER BY
[TotalDue]
FOR XML RAW('tr'), ELEMENTS
) AS NVARCHAR(MAX))
+ '
'
+ '
Top 10 By Due Date
Sales Order ID
Order Date
Customer ID
Total Due
'
+ 'Rendered: ' + CONVERT( nvarchar, GETDATE(), 121) + '
';
EXEC msdb.dbo.sp_send_dbmail 
@body = @body
, @recipients = 'avi@madeira.co.il'
, @subject = 'My Daily Report'
, @profile_name = 'MailReports'
, @body_format = 'HTML';
GO

The received email should look like this (I used TOP 10 to fit the email to a post size):

This output looks like any other report, but what does matter is the query under the hood. I recommend creating a stored procedure for each report. All that’s left is to add formatting and additional information as you like and schedule it.

The post Generating Email Reports using XML appeared first on Madeira Data Solutions.