<p>I am using sp_send_dbmail in SQL2005 to send an email with the results in an attachment. When the attachment is sent it is UCS-2 Encoded, I want it to be ANSI or UTF-8.</p>

<p>Here is the SQL</p>

<pre><code>EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'temp@example.com'
    , @query = 'DECLARE @string_to_trim varchar(60);SET @string_to_trim = ''1234''; select rtrim(@string_to_trim), ''tom'''
    , @query_result_header=0
    , @subject = 'see attach'
    , @body= 'temp body'
    , @profile_name= N'wksql01tAdmin'
    , @body_format = 'HTML'
    ,@query_result_separator = ','
    ,@query_attachment_filename = 'results.csv'
    ,@query_no_truncate = '0'
    ,@attach_query_result_as_file = 1
</code></pre>

<p>I have seen some comments on the internet that this is fixed with sql2005 SP2, but do not find it to be the case.</p>

[{'answer_id': 42728, 'author': 'Craig', 'author_id': 2894, 'author_profile': 'https://Stackoverflow.com/users/2894', 'pm_score': 1, 'selected': True, 'text': "<p>I think the only way to get around what you are seeing is to use BCP to dump the data to a flat file and then attach that file.  Sorry I couldn't be more help. :(</p>\n"}, {'answer_id': 5783390, 'author': 'bushtwig', 'author_id': 588229, 'author_profile': 'https://Stackoverflow.com/users/588229', 'pm_score': 0, 'selected': False, 'text': "<p>In order to have the file be ANSI/UTF-8 </p>\n\n<p>alter the sp_send_dbmail that lives in the <code>msdb</code> with this line along with the other variables:  <code>@ANSI_Attachment BIT = 0</code>\ni.e. </p>\n\n<pre><code>@mailitem_id INT = NULL OUTPUT,\n     @ANSI_Attachment BIT = 0\n     WITH EXECUTE AS 'dbo'\n</code></pre>\n\n<p>and then add this line to your call to sp_send_dbmail:</p>\n\n<pre><code>@ansi_attachment = 1\n</code></pre>\n\n<p>then it should give you an ansi attachment instead of unicode.</p>\n"}, {'answer_id': 14670451, 'author': 'Gena', 'author_id': 2036703, 'author_profile': 'https://Stackoverflow.com/users/2036703', 'pm_score': 4, 'selected': False, 'text': "<p>After some research on SQL Server 2008 R2:</p>\n\n<ol>\n<li><p>Add to <code>sp_send_dbmail</code>:</p>\n\n<pre><code>@ANSI_Attachment BIT = 0\nWITH EXECUTE AS 'dbo'\n</code></pre></li>\n<li><p>Replace</p>\n\n<pre><code>IF(@AttachmentsExist = 1)\n    BEGIN\n.......\n    END\n</code></pre>\n\n<p>with:</p>\n\n<pre><code>IF(@AttachmentsExist = 1)\nBEGIN\n    if (@ANSI_Attachment = 1) \n    begin\n        --Copy temp attachments to sysmail_attachments      \n        INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)\n        SELECT @mailitem_id, filename, filesize, \n                convert(varbinary(max), \n                    substring( -- remove BOM mark from unicode\n                        convert(varchar(max), CONVERT (nvarchar(max), attachment)), \n                        2, DATALENGTH(attachment)/2\n                    )\n                )\n\n        FROM sysmail_attachments_transfer\n        WHERE uid = @temp_table_uid\n    end else begin\n        --Copy temp attachments to sysmail_attachments      \n        INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)\n        SELECT @mailitem_id, filename, filesize, attachment\n        FROM sysmail_attachments_transfer\n        WHERE uid = @temp_table_uid\n    end\nEND\n</code></pre></li>\n</ol>\n"}]
['https://Stackoverflow.com/questions/42703', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']