使用sql發送郵件

USE [master]
GO
/****** 对象:  StoredProcedure [dbo].[sp_send_cdosysmail]    脚本日期: 02/25/2011 11:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_send_cdosysmail]
    @From varchar(100) ,
    @To varchar(5000) ,
    @Subject varchar(100)=" ",
    @Body varchar(8000) =" ",
                 @BodyType varchar(20)='TextBody'

    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar(255)
    Declare @description varchar(500)
    Declare @output varchar(1000)
                 IF @BodyType <> 'HtmlBody' SET @BodyType='TextBody'
 --************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
        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
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
                   RETURN
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            RETURN
          END
      END

 --***************Configuring the Message Object ******************
 -- This is to configure a remote SMTP server.
 -- mail.tuc-tech.com
    EXEC @hr = sp_OASetProperty @iMsg,  'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
        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
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END
 -- 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', 'mail.mektec-sz.com'
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

 -- Save the configurations to the message object.
    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')
     GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

 -- Set the e-mail parameters.
    EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

    EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

    EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

 -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg, @BodyType, @Body
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
        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
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

 -- Do some error handling after each step if you have to.
 -- Clean up the objects created.
        send_cdosysmail_cleanup:
 If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
 BEGIN
  EXEC @hr=sp_OADestroy @iMsg

  -- handle the failure of the destroy if needed
  IF @hr <>0
       BEGIN
   select @hr
                 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
          EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

   -- if sp_OAGetErrorInfo was successful, print errors
   IF @hr = 0
   BEGIN
    SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
   END

   -- else sp_OAGetErrorInfo failed
   ELSE
   BEGIN
    PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
   END
  END
 END
 ELSE
 BEGIN
  PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
  INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
         RETURN
 END