USP_GENERALPURPOSEEMAIL_RESETSTATUS

      CREATE procedure dbo.USP_GENERALPURPOSEEMAIL_RESETSTATUS
      (
        @QUEUEID uniqueidentifier,
        @ISERROR bit,
        @ERRORMESSAGE nvarchar(max),
        @PROCESSINGID uniqueidentifier = null,
        @MAXIMUMCLIENTPROCESSMINUTES int = null,
        @MAXIMUMNOQUEUESTATUSCHANGEMINUTES int = null
      )
      with execute as owner
      as
      begin
        set nocount on;
        declare @ExceptionXML as xml;

        update dbo.EMAILQUEUEGENERALPURPOSE set FIRSTPOLLSTARTED = 0 where ID = @QUEUEID and EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000' and (CHUNKPROCESSING < 2 or (RETRYNUMBER > 0 and CHUNKPROCESSING = 2)) and MERGEROWSPROCESSED < 1;

        if @ISERROR = 1
        begin
            update dbo.EMAILQUEUEGENERALPURPOSE set THREADID = '00000000-0000-0000-0000-000000000000', RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end), FIRSTPOLLSTARTED = 0,
                    MERGEROWSPROCESSED = case when CHUNKPROCESSING = 0 then 0 else MERGEROWSPROCESSED end
                    where ID = @QUEUEID;

          select @ExceptionXML = STATUSMESSAGE from dbo.EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @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.EMAILSTATUSGENERALPURPOSE set STATUSMESSAGE = @ExceptionXML, RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end)
            where EMAILQUEUEGENERALPURPOSEID = @QUEUEID;

        end
        else
        begin
          if @PROCESSINGID is null
          begin
            update dbo.EMAILQUEUEGENERALPURPOSE set THREADID = '00000000-0000-0000-0000-000000000000'
              where ID = @QUEUEID and (CHUNKPROCESSING > 0 or EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000');                 
            -- reset instance ids here

          end
          else
          begin
            update dbo.EMAILQUEUEGENERALPURPOSE set THREADID = '00000000-0000-0000-0000-000000000000'
              where ID = @QUEUEID and THREADID = @PROCESSINGID and (CHUNKPROCESSING > 0 or EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000');                 
          end

          if len(@ERRORMESSAGE) > 0
          begin
              -- now check if email job appears to be in a stuck condition, reset it, increment retry count

              -- we cannot use the passed in queue id here, because the job is not going to have been picked up,

              -- which is why it is, in fact, stuck.

              declare @STUCKLIST table
              (    
                STUCKID uniqueidentifier
              );


            insert into @STUCKLIST(STUCKID)
              select Q.ID from dbo.EMAILQUEUEGENERALPURPOSE Q
                                inner join dbo.EMAILSTATUSGENERALPURPOSE S on Q.ID = S.EMAILQUEUEGENERALPURPOSEID
                                where (datediff(n, S.CLIENTPROCESSSTARTDATE, getutcdate()) > @MAXIMUMCLIENTPROCESSMINUTES and datediff(n, S.DATECHANGED, getdate()) > @MAXIMUMNOQUEUESTATUSCHANGEMINUTES) and
                                      ((Q.THREADID <> '00000000-0000-0000-0000-000000000000' and Q.CHUNKPROCESSING = 0 and S.INTERNALJOBIDENTITY is null) or                   
                                      (S.STATUS not in (10,15,17)) or
                                      (Q.CHUNKPROCESSING > 0 or Q.MERGEROWSPROCESSED > 0 or S.INTERNALJOBIDENTITY is not null) or
                                (Q.CHUNKPROCESSING > 1000))

            if (select count(*) from @STUCKLIST) > 0
            begin
              update dbo.EMAILQUEUEGENERALPURPOSE set THREADID = '00000000-0000-0000-0000-000000000000', RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end), FIRSTPOLLSTARTED = case when EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000' then 0 else FIRSTPOLLSTARTED end, CHUNKPROCESSING = case when EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000' then 0 else CHUNKPROCESSING end, MERGEROWSPROCESSED = case when EXTERNALJOBIDENTITY = '00000000-0000-0000-0000-000000000000' then 0 else MERGEROWSPROCESSED end
                from dbo.EMAILQUEUEGENERALPURPOSE Q
                inner join @STUCKLIST L on Q.ID = L.STUCKID;

              select @ExceptionXML = STATUSMESSAGE from dbo.EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @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.EMAILSTATUSGENERALPURPOSE set STATUSMESSAGE = @ExceptionXML, RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end)
                from dbo.EMAILSTATUSGENERALPURPOSE S
                inner join @STUCKLIST L on S.EMAILQUEUEGENERALPURPOSEID = L.STUCKID;

              -- clean up merge data

              declare @RESETSQL nvarchar(1000);

              declare @QID uniqueidentifier;
              declare MERGECURSOR cursor fast_forward for
              select STUCKID from @STUCKLIST;

              open MERGECURSOR;

              fetch next from MERGECURSOR into @QID;
              while @@fetch_status = 0
              begin
                declare @MERGETABLE nvarchar(305) = (select dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QID));

                set @RESETSQL = 'if object_id(''' + @MERGETABLE + ''') is not null update ' + @MERGETABLE + ' set INSTANCEID = ''00000000-0000-0000-0000-000000000000'';'
                exec sp_executesql @RESETSQL;

                fetch next from MERGECURSOR into @QID;
              end   
              close MERGECURSOR;
              deallocate MERGECURSOR;       
            end
          end
        end     

      end