SQL 中调用Cdo.Message 发Email 乱码问题
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'中那個國',''