配置数据发送邮件

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.'