Monday, 22 February 2010

Get rid of padded spaces in queries attached with sp_send_dbmail

Have you tried to use sp_send_dbMail to send the results of a query as a csv file attachment?
If so, and it included some character fields, you have probably seen that the resulting file looks similar to this:

RecordId,Description                          ,category  
--------,-------------------------------------,----------
       1,banana                               ,        24
       2,strawberry                           ,       189

This is fine to view on a wide screen, but a pain to view on a narrow display. Also if you are sending a lot of data, the file will be much larger than necessary due to all the extra characters.

But there is an undocumented parameter for sp_send_dbmail that removes these spaces, and that is:

@query_result_no_padding = 1

Add this to your sp_send_dbmail statement, and suddenly your result set is transformed to just

RecordId,Description,category
--------,-----------,--------
1,banana,24
2,strawberry,189

This produces a much smaller file attachment size, and means you are less likely to be accused of bringing down the company's email server by sending it!

5 comments:

brelee said...

wow! I was looking for that parameter for weeks a year or two before. An now I found it! Thank You!

Brad McAllister said...

nice one, now some of my daily exports look far better..

RichardHemmings said...

Why is this not documented in BOL?! Thank-You.

RichardHemmings said...
This comment has been removed by the author.
Richard Brown said...

This is a great tip, I've spent ages trying to make my file look better, so thanks. And happy 6th birthday to your post too :)

Post a Comment