USP_GENERALPURPOSEEMAIL_RESET_MERGEROWINSTANCE

      CREATE procedure [dbo].[USP_GENERALPURPOSEEMAIL_RESET_MERGEROWINSTANCE]
      (
        @QUEUEID uniqueidentifier,
        @DATATABLENAME nvarchar(255),
        @IDTODELETE nvarchar(max),
        @IDCOUNT integer,
        @INSTANCEID uniqueidentifier
      )
      with execute as owner
      as
begin
      declare @MERGEROWSPROCESSED integer;
        set nocount on;

        -- need to ensure that rows are deleted, so deadlock handling is being added

      declare @RETRIES integer = 3;
      while @RETRIES > 0
      begin
        begin try
          begin transaction

        declare @RESETSQL as nvarchar(max);
          declare @MERGEROWTABLE as nvarchar(128);

          set @MERGEROWTABLE = dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QUEUEID);

        if len(@DATATABLENAME) > 0
        begin

          create table #MERGEID
          (
            ID uniqueidentifier
          )

          declare @IDOC int
          declare @TABLEPATH nvarchar(275)

          set @TABLEPATH = '/MERGEDATA/' + @DATATABLENAME
          exec sp_xml_preparedocument @IDOC output, @IDTODELETE

          insert into #MERGEID(ID)
            select MERGEROWID from openxml(@IDOC, @TABLEPATH, 2)
            with
            (
              MERGEROWID uniqueidentifier
            )

              set @RESETSQL = 'update dbo.EMAILQUEUEGENERALPURPOSE set MERGEROWSPROCESSED = MERGEROWSPROCESSED + ' + cast(@IDCOUNT as nvarchar(50)) + ' where ID = ''' + cast(@QUEUEID as nvarchar(36)) + ''';
                                            if object_id(''' + @MERGEROWTABLE + ''') is not null update ' + @MERGEROWTABLE + ' set INSTANCEID = ''00000000-0000-0000-0000-000000000000'' where INSTANCEID = ''' + cast(@INSTANCEID as nvarchar(36)) + ''' and MERGEROWID not in (select ID from #MERGEID);'

          exec sp_executesql @RESETSQL;

          exec sp_xml_removedocument @IDOC;

          drop table #MERGEID;
        end
        else
        begin
          set @RESETSQL = 'update dbo.EMAILQUEUEGENERALPURPOSE set MERGEROWSPROCESSED = MERGEROWSPROCESSED + ' + cast(@IDCOUNT as nvarchar(50)) + ' where ID = ''' + cast(@QUEUEID as nvarchar(36)) + ''';
            if object_id(''' + @MERGEROWTABLE + ''') is not null update ' + @MERGEROWTABLE + ' set INSTANCEID = ''00000000-0000-0000-0000-000000000000'' where INSTANCEID = ''' + cast(@INSTANCEID as nvarchar(36)) + '''';

          exec sp_executesql @RESETSQL;
        end
        set @MERGEROWSPROCESSED = (select top 1 MERGEROWSPROCESSED from dbo.EMAILQUEUEGENERALPURPOSE where ID = @QUEUEID and MERGEROWSPROCESSED >= 0)

        set @RETRIES = 0



            commit transaction
          end try
          begin catch
            if (ERROR_NUMBER() = 1205)
              set @RETRIES = @RETRIES - 1;
            else
              set @RETRIES = -1;

            if XACT_STATE() <> 0
              rollback transaction

          end catch
        end            

        update dbo.EMAILSTATUSGENERALPURPOSE set LASTMERGEROWPROCESSED = isnull(@MERGEROWSPROCESSED, LASTMERGEROWPROCESSED) where EMAILQUEUEGENERALPURPOSEID = @QUEUEID

      end