Problems with XP_SMTP_SendMail

• Kevin Post

  Old Hand

  Points: 321

  More actions

  March 9, 2006 at 1:38 pm

  #112159

  I'm having a problem with XP_SMTP_SendMail.  I use it to send messages from my SQL Server to customers to notify them of things like Customer Support call status updates.  Consequently, it is important to take steps to ensure that the messages are received by the customer when an error occurs.

  XP_SMTP_SendMail returns a 0 when everything works properly and returns a 1 if there is any error.  The error appears to be printed rather than returned in an OUTPUT parameter.  I've written a stored proc (see code below) that is nothing more than a wrapper for XP_SMTP_SendMail that checks for a non-zero ReturnCode and writes the e-mail and all the related fields to a table so that it can be resent later when the issue causing the error condition has been resolved.

  Here's my problem:  XP_SMTP_SendMail sometimes gives me a ReturnCode of 1 even when the e-mail is sent successfully to the customer.  Consequently, when I re-send the message(s), they are duplicates of the original message(s).  This really seems to upset some people. 

  I need to do some additional work to determine the problem before simply resending the messages.  Is there a way I can capture that printed error message returned by the XP_SMTP_SendMail proc into a variable so that I can write it to my FailedMail table along with the message?  I'm lost...

  Thanks!

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

   ** Send the email

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

   EXEC @ReturnCode = master.dbo.xp_smtp_sendmail

        @From   = @vchFromAddr,

        @From_Name  = @vchFromName,

        @To   = @vchTo,

           @ReplyTo         = @vchReplyTo,

        @CC   = @vchCC,

        @BCC   = @vchBCC,

        @Priority  = @vchPriority,

        @Subject  = @vchSubject,

        @Message  = @vchMessage,

        @MessageFile  = @vchMessageFile,

        @Type   = @vchType,

        @Attachment  = @vchAttachment,

        @Attachments  = @vchAttachments,

        @CodePage  = @iCodePage,

        @Server   = @vchServer

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

   ** Insert failed e-mails into a table to be resent later

   ** by cs_SendFailedMail.

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

   IF @ReturnCode <> 0

   BEGIN

    INSERT INTO FailedMail

    (

     vchFromAddr,

     vchFromName,

     vchTo,    

     vchReplyTo,

     vchCC,

     vchBCC,

     vchPriority,

     vchSubject,

     vchMessage,

     vchMessageFile,

     vchType,

     vchAttachment,

     vchAttachments,

     iCodePage,

     vchServer,

     vchError,

     dtInsertDate,

     chInsertBy)

    

    VALUES

    (

     @vchFromAddr,

     @vchFromName,

     @vchTo,

     @vchReplyTo,

     @vchCC,

     @vchBCC,

     @vchPriority,

     @vchSubject,

     @vchMessage,

     @vchMessageFile,

     @vchType,

     @vchAttachment,

     @vchAttachments,

     @iCodePage,

     @vchServer,

     @vchError,

     GETDATE(),

     'AUTO')