Send e-mail fra sql server

Der findes nogle muligheder for at sende e-mail fra en SQL 2000 server med fordele og ulemper:

  1. SQL e-mail.
    Det er sql serverens indbyggede e-mail forsendelse, som kræver at en e-mail klient er installeret på serveren. Det vil man ikke have!
  2. Egen stored procedure som benytter cdosys.
    cdosys er COM komponent. Det fungerer fint (se kode nedenunder), men giver en grim exception i .NET med indhold ‘A severe error occurred on the current command.  The results, if any, should be discarded’, når den anvendes med vedhæftninger.
  3. Tredje parts e-mail komponent.
    Jeg har gode erfaringer med denne: http://www.sqldev.net/xp/xpsmtp.htm.

 

Stored procedure, tilpasset til et specifikt system, for at sende e-mails vha. cdosys:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

ALTER       PROCEDURE [dbo].[proc_send_cdosysmail]
    @From varchar(100) = " " ,
    @To varchar(100) = " " ,
    @Subject varchar(100)=" ",
    @Body text = " ",
    @SmtpServer varchar(100) = " ",
    @Attachment varchar(1024) = null
/************************************************************
 
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References 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

2007-07-13, BNDR:
The code is adapted from
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839.
Error logging in TBLTrace.

2007-08-17, BNDR:
File attachment functionality added.
 
***********************************************************/
    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar(255)
    Declare @description varchar(500)
    Declare @output varchar(1000)
    Declare @message nvarchar(4000)
 
–************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate ‘CDO.Message’, @iMsg OUT
    IF @hr <>0
      BEGIN
        SELECT @hr
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OACreate’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OACreate’, @message)
                   RETURN
          END
        ELSE
          BEGIN
            PRINT ‘  sp_OAGetErrorInfo failed.’
            RETURN
          END
      END
 
–***************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′
    IF @hr <>0
      BEGIN
        SELECT @hr
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty sendusing’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty sendusing’, @message)
                   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’, @smtpServer
    IF @hr <>0
      BEGIN
        SELECT @hr
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty smtpserver’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty smtpserver’, @message)
                   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
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty Update’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty Update’, @message)
     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
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty To’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty To’, @message)
                   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
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty From’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty From’, @message)
                   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
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty Subject’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty Subject’, @message)
                   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, ‘TextBody’, @Body
    IF @hr <>0
      BEGIN
        SELECT @hr
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OASetProperty TextBody’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OASetProperty TextBody’, @message)
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT ‘  sp_OAGetErrorInfo failed.’
            GOTO send_cdosysmail_cleanup
          END
      END

    — Attach log file
    IF @Attachment is not null
     BEGIN
     EXEC sp_OAMethod @iMsg, ‘AddAttachment’, null, @Attachment
    END

           — Send the email
    EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL
    IF @hr <>0
      BEGIN
        SELECT @hr
        select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
        INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OAMethod Send’, @message)
        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
            select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
            INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OAMethod Send’, @message)
                   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
                 select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
                 INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘Failed at sp_OADestroy’, @message)
          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
                         select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
                         INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’, ‘sp_OAGetErrorInfo for sp_OADestroy’, @message)
   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.’
         select @message = ‘From: ‘ + isnull(@From, ”) + ‘ To: ‘ + isnull(@To, ”) + ‘ Subject: ‘ + isnull(@Subject, ”) + ‘ Description: ‘ + isnull(@description, ”)
         INSERT INTO [dbo].[TBLTrace] (GUID, Stamp, Type, ShortMessage, LongMessage) VALUES (‘not used’, getdate(), ‘ERROR in proc_send_cdosysmail’,
‘@iMsg is NULL, sp_OADestroy skipped’, @message)
         RETURN
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Leave a Reply

Your email address will not be published. Required fields are marked *