如何更改sp_send_dbmail查询的附件编码

Question

我使用sp_send_dbmail生成并发送给其他程序的文件。该程序消化"ANSI/ASCII"one_answers"ISO-8859-1"编码。但是我不能让sp_send_dbmail做一个。

过程调用看起来像这样

exec msdb.dbo.sp_send_dbmail 
    @profile_name= @profile_name, 
    @recipients  = @recipients,
    @body = @body,
    @subject = @subject,
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_result_no_padding = 1,
    @query = @query,
    @query_attachment_filename = @fname,
    @query_result_width = 4000,
    @mailitem_id = @mailitem_id OUTPUT

所以附件是根据传递的查询的结果创建的。但是由于某种原因实际附加到邮件中的结果文件是用UCS2小端码编码的。有办法改变吗?

Answer 1

找到了允许在UTF/ANSI之间切换的解决方案。为了做到这一点，你需要像这样修改sp_send_dbmail:

1. 为程序添加新参数，如@ANSI_Attachment BIT = 0
2. 替换它的一部分代码IF(@AttachmentsExist = 1) BEGIN ....... END

' IF (@ attachmentgender = 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 `

这做同样的，但如果在过程中调用@ANSI_Attachment = 1使用它删除unicode BOM标记之前发送。