When you use the sp_send_dbmail stored procedure to send emails from SQL Server, you have the option of adding query results to the email.

When you do this, you might find that unwanted padding has been added to some columns. Fortunately, you can eliminate this padding with the @query_result_no_padding argument.

Before

Here’s an example of what the results could look like with padding.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = 'Potential candidates for an Admin job, perhaps?',
    @query = 'SELECT TOP(5) * FROM Artists;',
    @execute_query_database = 'Music',
    @subject = 'Query results as discussed';

Result:

Potential candidates for an Admin job, perhaps?
ArtistId    ArtistName                                                
           ActiveFrom      
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------- ----------------
         1 Iron Maiden                                                   
                 1975-12-25
         2 AC/DC                                                          
                 1973-01-11
         3 Allan Holdsworth                                           
                 1969-01-01
         4 Buddy Rich                                                    
                 1919-01-01
         5 Devin Townsend                                            
                 1993-01-01

(5 rows affected)

In this case, there is so much padding going on that everything wraps over to the next line, and the headers don’t line up with the data.

After

Here’s an example of what the results look like after I’ve removed the padding.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = 'Potential candidates for an Admin job, perhaps?',
    @query = 'SELECT TOP(5) * FROM Artists;',
    @execute_query_database = 'Music',
    @subject = 'Query results as discussed';

Result:

Potential candidates for an Admin job, perhaps?
ArtistId ArtistName ActiveFrom
-------- ---------- ----------
1 Iron Maiden 1975-12-25
2 AC/DC 1973-01-11
3 Allan Holdsworth 1969-01-01
4 Buddy Rich 1919-01-01
5 Devin Townsend 1993-01-01

(5 rows affected)