当前位置:  开发笔记 > 前端 > 正文

sp_send_dbmail附件编码

如何解决《sp_send_dbmail附件编码》经验,为你挑选了1个好方法。

我在SQL2005中使用sp_send_dbmail发送包含附件中结果的电子邮件.发送附件时,它是UCS-2编码,我希望它是ANSI或UTF-8.

这是SQL

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

我在互联网上看到一些评论,这是用sql2005 SP2修复的,但是没有发现它是这种情况.



1> 小智..:

经过对SQL Server 2008 R2的一些研究:

    添加到sp_send_dbmail @ANSI_Attachment BIT = 0 WITH EXECUTE AS'dbo'

    更换

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

有:

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

推荐阅读
夏晶阳--艺术
这个屌丝很懒,什么也没留下!
DevBox开发工具箱 | 专业的在线开发工具网站    京公网安备 11010802040832号  |  京ICP备19059560号-6
Copyright © 1998 - 2020 DevBox.CN. All Rights Reserved devBox.cn 开发工具箱 版权所有