我在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修复的,但是没有发现它是这种情况.
经过对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