I'm using sp_send_dbmail in a SQL agent job triggered nightly. The job queries our database and checks for product price updates, if there are some, it will send them in an email as an attachment to our e-commerce vendor. They have some automatic processes they will update our e-commerce platform, however their automated process cannot deal with the file provided by sp_send_dbmail. sp_send_dbmail seems to be putting null characters between all characters in the result set.
See result of opening the csv in a hex editor:
Viewed through a text editor I see as expected:
sp_send_dbmail query here:
SET @FileName = 'Update_' + CONVERT(VARCHAR(12),GETDATE),105) '.csv'
SET @Query = 'Some Query'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@domain.com'
@query = @Query
@attach_query_result_as_file = 1
@query_attachment_filename = @FileName
@query_result_separator = ','
@query_result_no_padding = 1
END
What's going on here?
****EDIT FOR ADDITIONAL CLARIFICATION****
Using Microsoft SQL Server 2008 R2 Collation is Latin1_General_CI_AS
Leaving no_padding out leaves several trailing spaces in each returned field. each space (20) separated by a null (00)