question

46714091 avatar image
0 Votes"
46714091 asked Criszhan-msft edited

changing the endcoding of an attached file in sql trasaction

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

arvindviswakarma-5680 avatar image
0 Votes"
arvindviswakarma-5680 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.