Introduction

Jason Bohanon uses this script from time to time on his publisher. It performs a similar function to the Conflict resolution tool in Enterprise Manager. But it is different in that you can see the results on one page in the query editor. In those cases where there are erroneous conflicts between subscriptions, this script can help to weed through the huge list of update conflicts.

Script

/****** Object: StoredProcedure [dbo].[zzp_repl_create_conflict_repair_scripts] Script Date: 09/13/2006 11:32:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[zzp_repl_create_conflict_repair_scripts]
@table_name nvarchar(128),
@pub_name nvarchar(128),
@target_dt datetime = null
AS

/*
Generates an output of the conflicts in a given article name.
It will show the conflict value and the current value in an UPDATE statement that could fix the data on the publisher.
I found this particularly useful when I was dealing with a strange erroneous conflict problem.
*/

declare @table_db nvarchar(128),
@table_con nvarchar(128),
@sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@column_name nvarchar(128),
@rowguid uniqueidentifier,
@upd_sql nvarchar(4000),
@rpt_sql nvarchar(4000),
@del_sql nvarchar(4000),
@first_loop bit,
@conflict_type int,
@repl_time datetime

set nocount on

Select @table_db = @table_name

SELECT @table_con = conflict_table
from sysmergearticles
WHERE pubid = (select pubid from sysmergepublications where name = @pub_name)
and name = @table_db

declare @tbl_cols table
(col nvarchar(128),
mark bit default 0,
used bit default 0)

SET @upd_sql = 'UPDATE ' + @table_db + char(13) + char(10) + 'SET ' + char(13) + char(10)

INSERT INTO @tbl_cols
(col)
SELECT column_name
FROM information_schema.columns
WHERE TABLE_NAME = @table_db
ORDER BY ordinal_position

IF OBJECT_ID('tempdb..#tmp_rowguids') IS NOT NULL
DROP TABLE #tmp_rowguids

CREATE TABLE #tmp_rowguids
(rowguid uniqueidentifier,
conflict_type int,
repl_time datetime,
used bit default 0)

set @sql1 = 'SELECT rowguid, conflict_type, msrepl_create_time FROM [' + @table_con + ']' +
' WHERE conflict_type in (1, 2, 3, 4) ' +
CASE WHEN @target_dt is not null
THEN ' and msrepl_create_time > ' + '''' + CAST(@target_dt as nvarchar(32)) + ''''
ELSE ' '
END

INSERT INTO #tmp_rowguids
(rowguid, conflict_type, repl_time)
EXEC (@sql1)

IF OBJECT_ID('tempdb..#tmp_a') is not null
drop table #tmp_a

create table #tmp_a
(value nvarchar(512))

IF OBJECT_ID('tempdb..#tmp_b') is not null
drop table #tmp_b

create table #tmp_b
(value nvarchar(512))

WHILE EXISTS (select * from #tmp_rowguids where used = 0)
begin

select @rowguid = rowguid,
@conflict_type = conflict_type,
@repl_time = repl_time
from #tmp_rowguids
where used = 0
order by repl_time desc

SET @first_loop = 1

WHILE EXISTS
(SELECT 1
FROM @tbl_cols
WHERE used = 0)
BEGIN

DELETE FROM #tmp_a
DELETE FROM #tmp_b

SELECT @column_name = col
FROM @tbl_cols
WHERE used = 0

SET @sql1 = 'SELECT ' + @column_name + ' FROM [' + @table_db + '] WHERE rowguid = ' + '''' + CAST(@rowguid as nvarchar(40)) + ''''
SET @sql2 = 'SELECT ' + @column_name + ' FROM [' + @table_con + '] WHERE rowguid = ' + '''' + CAST(@rowguid as nvarchar(40)) + ''''

INSERT INTO #tmp_a
(value)
EXEC (@sql1)

INSERT INTO #tmp_b
(value)
EXEC (@sql2)

IF COALESCE((SELECT value FROM #tmp_a), '') <> COALESCE((SELECT value FROM #tmp_b), '')
BEGIN

SELECT @upd_sql = @upd_sql + CASE WHEN @first_loop = 1
THEN ' '
ELSE ','
END + ' ' + @column_name + ' = ' + '''' + COALESCE(b.value, 'NULL') + ''''
+ ' /*CURRENT VALUE:' + COALESCE(a.value, 'NULL') + '*/' + char(13) + char(10)

FROM #tmp_b b , #tmp_a a

UPDATE @tbl_cols
SET mark = 1
WHERE col = @column_name

SELECT @del_sql = 'DELETE FROM [' + @table_con + '] WHERE rowguid = ' + '''' + CAST(@rowguid as nvarchar(40)) + ''''
SET @first_loop = 0
END

UPDATE @tbl_cols
SET used = 1
WHERE col = @column_name

END
IF @first_loop = 1
BEGIN
set @upd_sql = '--Records Match '
set @del_sql = 'DELETE FROM [' + @table_con + '] WHERE rowguid = ' + '''' + CAST(@rowguid as nvarchar(40)) + ''''
END
SELECT @upd_sql = @upd_sql + 'WHERE rowguid = ' + '''' + CAST(@rowguid as nvarchar(40)) + ''''+
char(13) + char(10) +
char(13) + char(10) +
'/*RESOLUTION (1=upd, 0=not, 2=pending)' + char(13) + char(10) +
char(13) + char(10) +
'*/'

select '/******CONFLICT REPORT: ' + @table_name + '********'
select 'conflict date/time: ' + CAST(@repl_time as nvarchar(32))
select @conflict_type as 'conflict type'
select case when @conflict_type = 1 then 'Publisher and Subscriber both tried to update the same column.'
when @conflict_type = 2 then 'Publisher and Subscriber both tried to update the same row.'
when @conflict_type = 3 then 'One replica deleted a row while the other updated it; the delete wins.'
when @conflict_type = 4 then 'One replica deleted a row while the other updated it; the update wins.'
when @conflict_type = 5 then 'An insert at the subscriber couldn’t be made into the publisher.'
when @conflict_type = 6 then 'An insert at the publisher couldn’t be made into the subscriber.'
when @conflict_type = 7 then 'A delete at the subscriber couldn’t be made at the publisher.'
when @conflict_type = 8 then 'A delete at the publisher couldn’t be made at the subscriber.'
when @conflict_type = 9 then 'An update at the subscriber couldn’t be made into the publisher.'
when @conflict_type = 10 then 'An update at the publisher couldn’t be made into the subscriber.'
end as 'conflict desc'
select '**********/'
select @upd_sql
select @del_sql

update #tmp_rowguids
set used = 1
where rowguid = @rowguid

update @tbl_cols
set used = 0, mark = 0

select @upd_sql = 'UPDATE ' + @table_db + char(13) + char(10) + 'SET ' + char(13) + char(10)

end

GO