我在 SQL2005 中使用 sp_send_dbmail 发送一封电子邮件，并将结果放在附件中。发送附件时，它是 UCS-2 编码的，我希望它是 ANSI 或 UTF-8。
这是 SQL
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]'
, @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 的一些研究：
-
Add to sp_send_dbmail:
@ANSI_Attachment BIT = 0
WITH EXECUTE AS 'dbo'
-
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
