SQL 中调用Cdo.Message 发Email 乱码问题

talantlee 2010-12-17 04:25:55

SQL 服务器为英文的

现在Email 内容需包含繁体

-- exec Sp_SendMail @from,@pwd,@to,@cc,@bcc,@title,@body,@errMsg
CREATE procedure Sp_SendMail
@from varchar(100),
@pwd varchar(20),
@to varchar(100),
@cc varchar(300),
@bcc varchar(100),
@title nvarchar(100),
@body nvarchar(4000),
@errMsg varchar(200) out
as

Declare @hr int
declare @errnum as int
declare @object as int
set @errnum=0 --正确的
if isnull(@from,'')=''
begin
select @from = 'emailaddress@xxx.com.cn'
select @pwd = '1234'
end

set @bcc=''
EXEC @hr = sp_OACreate 'cdo.Message', @object OUT

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '192.168.150.251'

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/usemessageresponsetext").Value','1'

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @from
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@pwd
--EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/languagecode").Value','0x0804'

EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @object, 'To', @to
--EXEC @hr = sp_OASetProperty @object, 'BodyPart.Charset','big5'
EXEC @hr = sp_OASetProperty @object, 'Bcc', @bcc
EXEC @hr = sp_OASetProperty @object, 'Cc', @cc
EXEC @hr = sp_OASetProperty @object, 'From', @from
EXEC @hr = sp_OASetProperty @object, 'Subject', @title
EXEC @hr = sp_OASetProperty @object, 'BodyFormat', 'MailFormat.Html'
--EXEC @hr = sp_OASetProperty @object, 'BodyEncoding', 'UTF8'

EXEC @hr = sp_OASetProperty @object, 'HTMLBodyPart.Charset', 'Big5'
EXEC @hr = sp_OASetProperty @object, 'HtmlBody',@body

-- add attachment
--EXEC @hr = sp_OAMethod @object, 'AddAttachment', NULL

EXEC @hr = sp_OAMethod @object, 'Send', NULL

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
set @errnum=@hr
set @errMsg= @object
END
--PRINT 'success'
EXEC @hr = sp_OADestroy @object
set @errMsg=isnull(@errMsg,'')
GO

exec Sp_SendMail '','','dagger.li@nienmade.com.cn','','',N'中那個國',N'中那個國',''

...全文