USP_TRANSACTIONALEMAIL_RESETSTATUS

This will reset the THREADID to Guid.Empty as well as increment the retryattempt so that the email will be picked up on the next cycle.

Parameters

Parameter	Parameter Type	Mode	Description
@QUEUEID	uniqueidentifier	IN
@ERRORMESSAGE	nvarchar(255)	IN

Definition

Copy



      CREATE procedure dbo.USP_TRANSACTIONALEMAIL_RESETSTATUS
      (
        @QUEUEID uniqueidentifier,
        @ERRORMESSAGE nvarchar(255)
      )
      as
      begin
        set nocount on;
          update dbo.EMAILQUEUETRANSACTIONAL set THREADID = '00000000-0000-0000-0000-000000000000', RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end)
          where ID = @QUEUEID;

        declare @ExceptionXML as xml;

        select @ExceptionXML = STATUSMESSAGE from dbo.EMAILSTATUSTRANSACTIONAL where EMAILQUEUETRANSACTIONALID = @QUEUEID;

        if @ExceptionXML is null
            set @ExceptionXML = '<Errors></Errors>';

        if len((convert(nvarchar(max), @ExceptionXML))) < 1500
        begin
            set @ExceptionXML.modify('insert <Exception Message="{sql:variable("@ERRORMESSAGE")}"/> 
              as last into (/Errors)[1]');
        end  

        update dbo.EMAILSTATUSTRANSACTIONAL set STATUSMESSAGE = @ExceptionXML, RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end)
          where EMAILQUEUETRANSACTIONALID = @QUEUEID;
      end