Formatting dbo.sp_send_dbmail

Recently I received an integrations request to create a globally standardized inventory system for all clients and make projections based off of the current inventory counts and frequency of sales. During this process a data steward has requested the information that returns as NULL (data that never had a home in the standardized table) so that they can see if any client-side inventory items should be added into the standardized inventory list.

To do this I pulled the data from a staging table that returned NULL and used that query to formulate the data I wanted to send to the steward. The basic format requested was CSV. I decided to go about sending the mid-integrations data using msdb.dbo.sp_send_dbmail. Since dbmail has so many different parameters I won’t get into them but you can find more documentation on each parameter on the msdn site here. My sample code is below:

There are lots of parameters that you will notice, most of them being very obvious to sending mail like the profile name, recipients, copy recipients, email subject, and email body. But there is even more functionality that you are able to utilize, so let’s take a look.

• @query – a query ran by dbmail to be included in the email sent
• @attach_query_result_as_file – specifies whether the results of the query are returned as an attached file within the email
• @query_attachement_filename – specifies the filename to the attachement
• @query_result_header – specified whether the query results will include column headers or not
• @query_result_width – used for formatting the results of the query
• @query_result_separator – character used to separate the columns in the results
• @query_result_no_padding – specifies whether you want the results padded or not

This alone is very useful information and can get you very far but there’s one small thing that will mess everything up if you leave the information the way it is and it’s due to an exportation format from query results. Whenever you run a query you usually return them in “grid” format but dbmail likes to use the text results for it’s exporting needs. But why is this a problem? Let’s take a look.

When running a simple query and returning in grid mode you will receive the following results.

This is normally how we look at data when using SQL Server. Grid mode is the most similar result set to looking at an Excel spreadsheet. From this format we would assume that dbmail would understand and automatically map the data into each respective cell upon export, but this is definitely not the case. Since dbmail uses the text results let’s query in text mode and see what will cause the problem.

If dbmail was to export text EXACTLY the way it is now you would receive all shown data but you will also have a dashed line taking up all of row #2 on the Excel spreadsheet. This will also throw off how the new CSV document will export and render locally inside Excel because, even though we used the @query_result_separator parameter, the CSV document has a hard time figuring out that there are multiple columns and will bunch all exported data together into column “A”. This is obviously not what we want. To fix this you can use the small hack below to force Excel to recognize the separation.

What this is doing is that, by including “sep=,”, you are forcing Excel to understand that the file is comma delimited and will open properly. We will also set the @query_result_width to 32767 (the max number) mainly to alleviate any word wrap that may occur. Now when my data is sent off to the data steward during the integrations process he will receive a CSV document that will be much easier to manipulate and digest compared to the smashed mess of data that we produced earlier.