Resolving merge replication conflicts type 5 & 6

/********************************************************
<#
.SYNOPSIS
   Generates a script to help resolve type 5 & 6 conflicts
.DESCRIPTION
   When a record with the same primary key value(s) is 
   inserted on 2 replicated databases at the same time, a pair of 
   conflicts 5 and 6 will occur: Both copies can not be transfered to 
   the database that already holds the other copy. The only feasible 
   way to resolve this is to remove one copy and have the other copy 
   re-replicated to make it available in all locations. Usually it is
   arbitrary which copy is maintained, so we pick the easiest method:
   delete the copy on the hub (= upload failed) and have the copy on 
   the publisher sent out to all hubs (most hubs will already have 
   the publisher's copy).

   A complication can occur when a foreign key exists referencing the
   row to delete. Although the value will 'come back' once the conflict 
   is resolved, we need to delete it with the referencing values still
   in place. Merge replication can do so because it ignores all foreign 
   key constraints, but for us the best way to do so is to temporary 
   disable the foreign keys. Don't forget to re-enable those constraints
   when you're done! A separate script exists that can be used to 
   disable/enable a table's foreign keys if you encounter this issue.

   The way this works:
   - Run this script on the publisher to generate from the replication
   conflicts a statement that can be copied to and run on the 
   subscriber(s). Run the generated script (unaltered) on each hub that
   has at least one conflicting row (the name of the conflicting hubs is 
   listed in the generated script).
   - Once you've verified that the hub has indeed one or more of the 
   conflicting rows, you can alter the generated script from a select
   statement into a delete statement: simply comment the line with 
   "--select *"   and uncomment the line with "--delete" and run the 
   delete statement on this hub. If a foreign key exists, the delete 
   will fail and you'll have to disable the foreign key before retrying 
   the delete. And enable it after the delete (without checking the
   existing values!)
   - After you've deleted all conflicting rows from the tables on all 
   hubs you can go back into Conflict Viewer and click 'Submit winner' 
   on all 'download' conflicts for this table and click 'Remove' on 
   all 'upload' conflicts.
.EXAMPLE
   No example available
.TAGS
   Download insert failed, replication, merge replication, 
   conflict, 
   Upload Insert Failed
********************************************************/-- type 5: upload insert failed - chosen solution: submit winner (= delete from office hub and re-submit from central)
-- type 6: download insert failed - chosen solution: submit loser (= delete from office hub and re-submit from central)



set nocount on;


declare @stmt nvarchar(max);


if object_id('tempdb..#conflict_values') is not null
   drop table #conflict_values;

-- Temporary table to hold all key with rowguid values for the affected rows on the publisher.
create table #conflict_values (
   pubid uniqueidentifier not null,
   nickname int not null,
   MSrepl_create_time datetime not null,
   [values] nvarchar(max) not null
);

-- Generate a statement to retrieve literal values from the conflict tables, specific 
-- for each table. The columns to retrieve are all key columns + the rowguid column.
with cteKeyColumns as (
   select ix.object_id, ic.column_id
   from sys.indexes ix
      inner join sys.index_columns ic on (ic.object_id = ix.object_id and ic.index_id = ix.index_id and ic.is_included_column = 0)
--   where ix.name = 'ixTransportLegs'
   where ix.is_primary_key = 1
),
cteRowGuidColumns as (
   select col.object_id, col.column_id
   from sys.columns col
   where col.is_rowguidcol = 1
),
cteTypesToLiteral as (
   select 
      --ut.system_type_id, 
      --ut.user_type_id, 
      ut.*,
      case st.name
         --when 'image' then 'binary'
         when 'text' then 'text'
         when 'uniqueidentifier' then 'uniqueidentifier'
         when 'date' then 'datetime'
         when 'time' then 'datetime'
         --when 'datetime2' then
         --when 'datetimcrlffset' then
         when 'int' then 'number'
         when 'tinyint' then 'number'
         when 'smallint' then 'number'
         when 'smalldatetime' then 'datetime'
         when 'real' then 'number'
         when 'money' then 'number'
         when 'datetime' then 'datetime'
         when 'float' then 'number'
         --when 'sql_variant' then
         when 'ntext' then 'ntext'
         when 'bit' then 'number'
         when 'decimal' then 'number'
         when 'numeric' then 'number'
         when 'smallmoney' then 'number'
         when 'bigint' then 'number'
         --when 'varbinary' then 'binary'
         when 'varchar' then 'text'
         --when 'binary' then 'binary'
         when 'char' then 'text'
         when 'timestamp' then 'number'
         when 'nvarchar' then 'ntext'
         when 'nchar' then 'ntext'
         when 'xml' then 'ntext'
      end as literal_type
   from sys.types ut
      left outer join sys.types st on (st.user_type_id = ut.system_type_id)
)
select @stmt =  
stuff((
   select N'' 
      + s.crlf + N'insert #conflict_values(pubid, nickname, MSrepl_create_time, [values])'
      + s.crlf + N'select ci.pubid,'
      + s.crlf + N' ci.tablenick,'
      + s.crlf + N' ci.MSrepl_create_time,'
      + s.crlf + N' stuff(('
      + s.crlf + N'      select '','' + t.txt as [text()]'
      + s.crlf + N'      from ('
         + stuff((
               select
        s.crlf + N'            union all select '
                  + convert(varchar(36), col.column_id) + ', '
                  + case ttl.literal_type
                     when 'datetime' then 'isnull(''{ts '''''' + convert(varchar(36), c.' + quotename(col.name) + ', 121) + ''''''}'', ''NULL'')'
                     when 'ntext' then 'isnull(N'''''''' + replace(c.' + quotename(col.name) + ', '''''''', '''''''''''') + '''''''', ''NULL'')'
                     when 'number' then 'isnull(convert(varchar(36), c.' + quotename(col.name) + '), ''NULL'')'
                     when 'text' then 'isnull('''''''' + replace(c.' + quotename(col.name) + ', '''''''', '''''''''''') + '''''''', ''NULL'')'
                     when 'uniqueidentifier' then 'isnull('''''''' + convert(varchar(36), ' + case col.is_rowguidcol when 1 then 'case ci.conflict_type when 5 then tbl.' + quotename(col.name) + ' when 6 then c.' + quotename(col.name) +' end' when 0 then 'c.' + quotename(col.name) end + ') + '''''''', ''NULL'')'
                  end
               from sys.columns col
                  left outer join cteTypesToLiteral ttl on (ttl.user_type_id = col.user_type_id)
               where col.object_id = sma.objid
                  and col.column_id in (
                        select kcn.column_id
                        from cteKeyColumns kcn
                        where kcn.object_id = sma.objid
                        union all
                        select rgc.column_id
                     )
               order by col.column_id
               for xml path(''), type
            ).value('.','nvarchar(max)'), 1, 24, '') + '  + ''  /* '' + ci.origin_datasource + '', conflict_type = '' + convert(varchar(36), ci.conflict_type) + '', reason_code = '' + convert(varchar(36), ci.reason_code) + '' */ '''
      + s.crlf + N'         ) t (column_id, txt)'
      + s.crlf + N'      order by t.column_id'
      + s.crlf + N'      for xml path(''''), type'
      + s.crlf + N'   ).value(''.'',''nvarchar(max)''), 1, 1, '''')'
      + s.crlf + N'from dbo.msmerge_Conflicts_info ci'
      + s.crlf + N'   inner join ' + 'dbo.' + quotename(sma.conflict_table) + ' c on (c.' + quotename(col_name(rgc.object_id, rgc.column_id)) + ' = ci.rowguid)'
      + s.crlf + N'   left outer join ' + quotename(object_schema_name(sma.objid)) + '.' + quotename(object_name(sma.objid)) + ' tbl on ('
         + stuff((
               select 
                  case col.is_nullable
                     when 0 then ' and tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' = c.' + quotename(col_name(col.object_id, col.column_id)) 
                     when 1 then ' and ((tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' is null and c.' + quotename(col_name(col.object_id, col.column_id)) + ' is null) or tbl.' + quotename(col_name(col.object_id, col.column_id)) + ' = c.' + quotename(col_name(col.object_id, col.column_id)) + ')'
                  end as [text()]
               from sys.columns col
               where col.object_id = sma.objid
                  and col.column_id in (
                        select kcn.column_id
                        from cteKeyColumns kcn
                        where kcn.object_id = sma.objid
                     )
               order by col.column_id
               for xml path(''), type
            ).value('.','nvarchar(max)'), 1, 5, '') + ')'
      + s.crlf + 'where ci.conflict_type in (5,6);' as [text()]
   from (
         select char(13) + char(10)
      ) s (crlf)
      cross join dbo.sysmergearticles sma
      left outer join cteRowGuidColumns rgc on (rgc.object_id = sma.objid)
   where exists (
         select *
         from dbo.msmerge_Conflicts_info ci
         where ci.conflict_type in (5,6)
            --and ci.reason_code = 2627 -- Primary key violation
            --                     -- = 2601 -- Cannot insert duplicate key row in object ....'
            --                     -- = 3 
            and ci.tablenick = sma.nickname
            and ci.pubid = sma.pubid
      )
   for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '');

--select @stmt as [text()]
--for xml path(''), type;

if @stmt is not null
   exec sp_executesql @stmt;

--select @stmt;


with cteKeyColumns as (
   select ix.object_id, ic.column_id
   from sys.indexes ix
      inner join sys.index_columns ic on (ic.object_id = ix.object_id and ic.index_id = ix.index_id and ic.is_included_column = 0)
--   where ix.name = 'ixTransportLegs'
   where ix.is_primary_key = 1
),
cteRowGuidColumns as (
   select col.object_id, col.column_id
   from sys.columns col
   where col.is_rowguidcol = 1
)
select 
     s.crlf + N'--delete t'
   + s.crlf + 'select t.*'
   + s.crlf + 'from ' + isnull( sma.destination_owner + '.', '') + quotename(sma.destination_object) + ' t'
   + s.crlf + ' inner join ('
   + stuff((
         select 
     s.crlf + '    union select ' + v.[values] as [text()]
         from #conflict_values v
         where v.pubid = sma.pubid
            and v.nickname = sma.nickname
         order by v.MSrepl_create_time
         for xml path(''), type
      ).value('.','nvarchar(max)'), 1, 12, '')
   + s.crlf + ' ) s (' + isnull(
         stuff((
               select ',' + quotename(col.name) as [text()]
               from sys.columns col
               where col.object_id = sma.objid
                  and col.column_id in (
                        select kcn.column_id
                        from cteKeyColumns kcn
                        where kcn.object_id = sma.objid
                        union all
                        select rgc.column_id
                     )
               order by col.column_id
               for xml path(''), type
            ).value('.','nvarchar(max)'), 1, 1, ''), 'ERROR - NO COLUMNS FOUND') + ') on ('
      + stuff((
            select 
               s.crlf +
                  case col.is_nullable
                     when 0 then ' and s.' + quotename(col.name) + ' = t.' + quotename(col.name) 
                     when 1 then ' and ((s.' + quotename(col.name) + ' is null and t.' + quotename(col.name) + ' is null) or s.' + quotename(col.name) + ' = t.' + quotename(col.name) + ')'
                  end as [text()]
               --s.crlf + ' and s.' + quotename(col.name) + ' = t.' + quotename(col.name) as [text()]
            from sys.columns col
            where col.object_id = sma.objid
               and col.column_id in (
                     select kcn.column_id
                     from cteKeyColumns kcn
                     where kcn.object_id = sma.objid
                  )
            order by col.column_id
            for xml path(''), type
         ).value('.','nvarchar(max)'), 1, 7, '')
   + ')'
   + s.crlf + 'where not ' + 's.' + quotename(col_name(rgc.object_id, rgc.column_id)) + ' = ' + 't.' + quotename(col_name(rgc.object_id, rgc.column_id)) as [text()]
from (
      select char(13) + char(10)
   ) s (crlf)
   cross join dbo.sysmergearticles sma
   left outer join cteRowGuidColumns rgc on (rgc.object_id = sma.objid)
where exists (
      select *
      from dbo.msmerge_Conflicts_info ci
      where ci.pubid  = sma.pubid
         and ci.tablenick = sma.nickname
         and ci.conflict_type in (5,6)
   )
for xml path(''), type;