changing the endcoding of an attached file in sql trasaction

Question

Hi,
I have written a sql server procedure which sends the result of a query as a csv file via e-mail.
SQL server generates the attached file in UTF-8 BOM encoding.
I need the attached file to be generated in UTF-8 (without BOM).
I tried to change the encoding using the command:

EXEC msdb.dbo.sysmail_configure_sp 'DefaultAttachmentEncoding', 'UTF-8';

before the comand:

EXEC msdb.dbo.sp_send_dbmail

but it doesn't work.
Is there someone who can help me?

Many thanks.

Answer 1

I too had this issue where the csv files had all row data in single column after opening in excel. I tried several ways to fix it from SQL but wasn't successful.
Finally I got some PowerShell code to fix the csv files once generated, probably from here:
https://stackoverflow.com/questions/5596982/using-powershell-to-write-a-file-in-utf-8-without-the-bom

Answer 2

Hi,

The document about the sysmail_configure_sp does not list the values that can specify for the parameter 'DefaultAttachmentEncoding'. It seems that specifying "UTF-8" is invalid, or that this option is not supported.

I saw some similar posts, the solution is to use powershell to generate attachments and then send the attachments.
https://dba.stackexchange.com/questions/284545/database-mail-has-incorrect-encoding-of-csv-attachment
https://dba.stackexchange.com/questions/6290/is-there-a-way-to-force-sp-send-dbmail-to-use-ansi-or-to-not-include-the-unicod

Answer 3

I see this is an old question, but posting my solution anyway. I was struggling with the same issue, except my attachment was being generated using UTF-16 LE BOM, and I too wanted UTF-8. Since sysmail_configure_sp has no effect on the encoding of the attachment <insert surprised emoji here> I had to find a different solution.

Turns out Excel does support UTF-16 / UTF-16 BOM for "seperated values" files, but only if the seperator being used is the TAB character.

The following code snippet works to generate an email with an attachment that opens up with proper columns in Excel:

DECLARE @tab CHAR
SET @tab = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name',
							 @recipients = 'person@company.com',
							 @subject = N'Excel does not excel at UTF-16',
							 @body = 'This attachment works, I promise!',
							 @query = 'SET NOCOUNT ON; SELECT ''"'' + T1.column1 + ''"'', ''"'' + T1.column2 + ''"'' FROM dbo.table1 AS [T1]',
							 @attach_query_result_as_file = 1,
							 @query_result_separator = @tab,
							 @query_result_no_padding = 1;