配置数据发送邮件

来源：互联网发布：nodejs和java 编辑：程序博客网时间：2024/05/21 09:54

IF EXISTS ( SELECT 1
FROM sys.procedures
WHERE object_id = OBJECT_ID('sys_sendmail') )
BEGIN
DROP PROCEDURE sys_sendmail
END
GO

/*
* Designer: Jekey
* Description:
* Created:
* History:
* =============================================================================
* Author DateTime Alter Description
* =============================================================================
*/
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'Ole Automation Procedures', 1 ;
GO
RECONFIGURE ;
GO

CREATE PROCEDURE sys_sendmail
@From VARCHAR(100) ,
@To VARCHAR(100) ,
@Bcc VARCHAR(500) = '' ,
@Subject VARCHAR(400) = '' ,
@Body VARCHAR(MAX) = ''
AS
BEGIN
DECLARE @object INT
DECLARE @hr INT

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',
'mail.suryani.cn'

--下面三条语句是smtp验证，如果服务器需要验证，则必须要这三句，你需要修改用户名和密码
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',
'Jekey.lin'
EXEC @hr = sp_OASetProperty @object,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',
'suryani007'

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

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

--判断出错
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN @object
END
PRINT 'success'
EXEC @hr = sp_OADestroy @object
END
GO

--EXEC sys_sendmail @From='xxx@163.com',@To='xxx@163.com',@Subject='test',@Body = 'This a example for SQL Send eamil.'

0 0