If you’ve ever used SQL Server’s Database Mail to email the results of a query, you might have noticed that the results are separated by a space by default.

This is fine if you want a space-separated result set, but what if you want it to be comma-separated?

Fortunately you can use the @query_result_separator argument to do just that. You can use this argument to specify any separator (as long as it’s a char(1)).

Example

Here’s an example to demonstrate how to change the separator to a comma:

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',
    @query_result_separator = ',',
    @query_result_no_padding = 1,
    @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)

Notice that I’m also using the @query_result_no_padding argument to remove any padding that might be applied to the results.

You can also use @attach_query_result_as_file = 1 to attach the results in a separate file if required.

An Example using the Default Space Separator

If I remove the @query_result_separator = ',' part from the above example, my results are separated by the default space character.

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',
    @query_result_no_padding = 1,
    @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)