I have a procedure that generates a resultset, which I attach as a CSV file attachment in sp_sendmail. However, the resulting file is encoded UCS-2 LE BOM (according to notepad++). This causes the resulting file to not "parse" into columns but opens with all values in column A. If I change the encoding to UTF-8 it opens fine.
This is an easy fix for me... but the recipients would have no idea how to do this. Can I change the file to be produced as UTF-8?
Code used to produce send the mail:
exec msdb.dbo.sp_send_dbmail @subject = @subject , @body = @message , @body_format = 'html' , @recipients = @recipients_to , @copy_recipients = @recipients_cc , @importance = 'High' , @sensitivity = 'Confidential' , @query = N'select * from ##Report' , @execute_query_database = @dbName , @attach_query_result_as_file = 1 , @query_attachment_filename = @file_name , @query_result_header = 1 , @query_result_no_padding = 1 , @query_result_separator = ',' , @query_result_width = 32767;