SQL发送邮件

发送邮件存储过程

 

CREATE PROCEDURE [dbo].[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.tom.com'

    SET @userName = '******@tom.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

 

##################################################

 

业务数据处理存储过程

CREATE PROCEDURE [dbo].[SP_Notify_DBA]
AS
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @a NVARCHAR(80)
        DECLARE @b NVARCHAR(80)
        DECLARE @c NVARCHAR(80)
        DECLARE @d NVARCHAR(80)

        DECLARE @str_send NVARCHAR(2000)

 

--声明for dcs表游标
        DECLARE cursor_for_me CURSOR scroll DYNAMIC
            FOR select  a, b, c, d  from    mytable  where   ( day >  ( GETDATE() - 12 ) )
                        
        SET @str_send = '邮件发送内容： ' + '<hr>'

--打开游标
        OPEN cursor_for_me

--循环获取游标
        FETCH NEXT FROM cursor_for_me  INTO @a, @b, @c, @d
        WHILE( @@fetch_status = 0 )
            BEGIN
                SET @str_send = @str_send + @a+  ' , ' + @b +  ' , ' + @c +  ' , '  + @d + '<hr>'

                FETCH NEXT FROM cursor_for_me  INTO @a, @b, @c, @d
            END

--关闭游标
        CLOSE cursor_for_me
--删除游标
        DEALLOCATE cursor_for_me

PRINT @str_send

 

--调用sys_sendmail发送邮件
        EXEC [dbo].[sys_sendmail] @From = N'******@tom.com',
            @To = N'******@163.com', @Bcc = N'',
            @Subject = N'邮件标题头', @Body = @str_send

    END

 

 

 

 

##################################################

 

 

 

若执行出现如下提示

“SQL Server 阻止了对组件 'Ole Automation Procedures' 的 过程'sys.sp_OACreate' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ole Automation Procedures'。有关启用 'Ole Automation Procedures' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。 语句已终止。”

可执行以下语句

 

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO