|
Introduction
Jason 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 |
|
