sp_send_dbmail attachment encoding

问题:

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.

Here is the SQL

EXEC msdb.dbo.sp_send_dbmail
    @recipients = '[e-mail]'
    , @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

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

回答1:

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. :(

回答2:

After some research on SQL Server 2008 R2:

1. Add to sp_send_dbmail:

   @ANSI_Attachment BIT = 0
   WITH EXECUTE AS 'dbo'
   

2. Replace

   IF(@AttachmentsExist = 1)
       BEGIN
   .......
       END
   

   with:

   IF(@AttachmentsExist = 1)
   BEGIN
       if (@ANSI_Attachment = 1) 
       begin
           --Copy temp attachments to sysmail_attachments      
           INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
           SELECT @mailitem_id, filename, filesize, 
                   convert(varbinary(max), 
                       substring( -- remove BOM mark from unicode
                           convert(varchar(max), CONVERT (nvarchar(max), attachment)), 
                           2, DATALENGTH(attachment)/2
                       )
                   )
   
           FROM sysmail_attachments_transfer
           WHERE uid = @temp_table_uid
       end else begin
           --Copy temp attachments to sysmail_attachments      
           INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
           SELECT @mailitem_id, filename, filesize, attachment
           FROM sysmail_attachments_transfer
           WHERE uid = @temp_table_uid
       end
   END
   

回答3:

In order to have the file be ANSI/UTF-8

alter the sp_send_dbmail that lives in the msdb with this line along with the other variables: @ANSI_Attachment BIT = 0 i.e.

@mailitem_id INT = NULL OUTPUT,
     @ANSI_Attachment BIT = 0
     WITH EXECUTE AS 'dbo'

and then add this line to your call to sp_send_dbmail:

@ansi_attachment = 1

then it should give you an ansi attachment instead of unicode.