【Vegas原创】SQL Server调用CDO发送邮件

最新推荐文章于 2022-03-25 16:42:50 发布

weixin_34228387

最新推荐文章于 2022-03-25 16:42:50 发布

阅读量121

点赞数

•用于某事件过期mail提醒。

操作步骤：

1.新建DailyJob,事件中填写：

exec dbo.EIP_DeadLine_Notice

/* ********************************************************************

*             Vegas Lee  制作       2006.01.17                       *

********************************************************************* */
CREATE    PROCEDURE dbo.EIP_DeadLine_Notice AS
DECLARE    @mailTitle nvarchar ( 100 ), @mailBody nvarchar ( 4000 ), @theMailList nvarchar ( 500 ), @C_mailBody nvarchar ( 4000 )

-- 项目到期时，系统自动发mail提醒==========================================
DECLARE Cur_EIP_DeadLine   CURSOR FOR
select t.Unique_ID,m1.name as From_Name,t.Category,t.Name,t.DeadLine,t.Description,t.people,t.finish,m.mail,m1.mail as from_mail from todo t,members m,members m1 where datediff (dd,t.DeadLine, getdate ()) < 1 and t.finish = 0 and t.people = m.Account_ID and m1.Account_ID = t.User_Account

Set @C_mailBody = N ' <HTML>... '
Set @C_mailBody = @C_mailBody + '<tr>... '
DECLARE @v_Unique_ID INT , @v_From_Name VARCHAR ( 10 ), @v_Category VARCHAR ( 10 )
DECLARE @v_Name VARCHAR ( 50 ), @v_DeadLine nvarchar ( 20 ), @v_Description VARCHAR ( 1000 ), @v_people nvarchar ( 10 ), @v_finish INT , @v_email VARCHAR ( 100 ), @v_from_mail VARCHAR ( 100 )

OPEN   Cur_EIP_DeadLine
FETCH Cur_EIP_DeadLine
INTO @v_Unique_ID , @v_From_Name , @v_Category , @v_Name , @v_DeadLine , @v_Description , @v_people , @v_finish , @v_email , @v_from_mail

WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
     Set @mailTitle = ' 系统通知： '
     Set @mailBody = ' <HTML>.. '

Set @C_mailBody = @C_mailBody + '...'
     SET @theMailList = rtrim ( @v_email )


     EXEC sp_send_cdosysmail @v_from_mail , @theMailList , @mailTitle , @mailBody


FETCH Cur_EIP_DeadLine INTO @v_Unique_ID , @v_From_Name , @v_Category , @v_Name , @v_DeadLine , @v_Description , @v_people , @v_finish , @v_email , @v_from_mail
         END

CLOSE Cur_EIP_DeadLine
DEALLOCATE Cur_EIP_DeadLine

-- ---=====================通知
Set @C_mailBody = @C_mailBody + ' </table></body></html> '

SET @theMailList = @v_from_Mail
Exec sp_send_cdosysmail_gb @v_from_Mail , @theMailList , @mailTitle , @C_mailBody

GO

3. sp_send_cdosysmail_gb:

CREATE PROCEDURE [ dbo ] . [ sp_send_cdosysmail_gb ]
    @From varchar ( 100 ) ,
    @To varchar ( 300 ) ,
    @Subject varchar ( 100 ) = " ",
    @Body nvarchar ( 4000 ) = " "
    -- @Body ntext
/* ********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

********************************************************************** */
    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar ( 255 )
    Declare @description varchar ( 500 )
    Declare @output varchar ( 1000 )

-- ************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate ' CDO.Message ' , @iMsg OUT

-- ***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
    EXEC @hr = sp_OASetProperty @iMsg , ' Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value ' , ' 2 '
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
    EXEC @hr = sp_OASetProperty @iMsg , ' Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value ' , '[Mailserver] '

-- Save the configurations to the message object.
    EXEC @hr = sp_OAMethod @iMsg , ' Configuration.Fields.Update ' , null

-- Set the e-mail parameters.
    EXEC @hr = sp_OASetProperty @iMsg , ' To ' , @To
    EXEC @hr = sp_OASetProperty @iMsg , ' From ' , @From
    EXEC @hr = sp_OASetProperty @iMsg , ' Subject ' , @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg , ' HTMLBody ' , @Body
    EXEC @hr = sp_OASetProperty @iMsg , ' HTMLBodyPart.Charset ' , ' gb2312 '
    EXEC @hr = sp_OAMethod @iMsg , ' Send ' , NULL

-- Sample error handling.
    IF @hr <> 0
      select @hr
      BEGIN
        EXEC @hr = sp_OAGetErrorInfo NULL , @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '   Source: ' + @source
            PRINT    @output
            SELECT @output = '   Description: ' + @description
            PRINT    @output
          END
        ELSE
          BEGIN
            PRINT '   sp_OAGetErrorInfo failed. '
            RETURN
          END
      END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
    EXEC @hr = sp_OADestroy @iMsg

GO

sp_send_cdosysmail(big5)

CREATE PROCEDURE [ dbo ] . [ sp_send_cdosysmail ]
    @From varchar ( 100 ) ,
    @To varchar ( 300 ) ,
    @Subject varchar ( 100 ) = " ",
    @Body nvarchar ( 4000 ) = " "
    -- @Body ntext
/* ********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

********************************************************************** */
    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar ( 255 )
    Declare @description varchar ( 500 )
    Declare @output varchar ( 1000 )

-- ************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate ' CDO.Message ' , @iMsg OUT

-- ***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
    EXEC @hr = sp_OASetProperty @iMsg , ' Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value ' , ' 2 '
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
    EXEC @hr = sp_OASetProperty @iMsg , ' Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value ' , '[MailServer]'

-- Save the configurations to the message object.
    EXEC @hr = sp_OAMethod @iMsg , ' Configuration.Fields.Update ' , null

-- Set the e-mail parameters.
    EXEC @hr = sp_OASetProperty @iMsg , ' To ' , @To
    EXEC @hr = sp_OASetProperty @iMsg , ' From ' , @From
    EXEC @hr = sp_OASetProperty @iMsg , ' Subject ' , @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg , ' HTMLBody ' , @Body
    -- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset','utf8'
    EXEC @hr = sp_OAMethod @iMsg , ' Send ' , NULL

-- Sample error handling.
    IF @hr <> 0
      select @hr
      BEGIN
        EXEC @hr = sp_OAGetErrorInfo NULL , @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '   Source: ' + @source
            PRINT    @output
            SELECT @output = '   Description: ' + @description
            PRINT    @output
          END
        ELSE
          BEGIN
            PRINT '   sp_OAGetErrorInfo failed. '
            RETURN
          END
      END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
    EXEC @hr = sp_OADestroy @iMsg

GO

4.查询ole automation procedures.txt

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

-- -查询状态
EXEC sp_configure ' Ole Automation Procedures ' ;
GO