MS SQL 发送邮件(更新版)

因工作需要,在数据库完成某个操作后,如果失败要通知相关人员.在网上看到的例子大多数都不检查SSL安全登录,因而修改后就放上来了,看红色是修改过的地方.

CREATE PROCEDURE sys_sendmail
  @From varchar(100),
  @To varchar(100),
  @Bcc varchar(500),
  @Subject varchar(400)=" ",
  @Body ntext =" "
AS

DECLARE @object int,@hr int
DECLARE @serverHost nvarchar(100),@userName nvarchar(100), @passWord nvarchar(50),@useSSL int
DECLARE @ssl nvarchar(10),@useServerCredentials int

set @serverHost='smtp.126.com'

set @userName='smd@126.com'
set @passWord='***'
set @useSSL=0
set @useServerCredentials=1
if @useSSL=1
begin
    set @ssl='true'
end
else
begin
    set @ssl='false'
end
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', @serverHost
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").Value', @ssl

if @useServerCredentials=1
begin
    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',@userName
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@passWord
end

EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @object, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @object, 'Send', NULL

--判断出错
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object 
--   RETURN @object
END

EXEC @hr = sp_OADestroy @object

 

GO