create procedure sys.sp_MSscript_update_pubwins
(
@publication sysname -- publication name
,@article sysname -- article name
,@objid int -- object id
)
as
begin
declare @cmd nvarchar(4000)
,@artid int
,@pubid int
,@qualname nvarchar(517)
,@dest_table sysname
,@dest_owner nvarchar(260)
,@rc int
,@fhasnonpkuniquekeys int
-- initialize the vars we will use
select @pubid = pubid from syspublications where name = @publication
select @artid = artid, @dest_table = dest_table, @dest_owner = dest_owner
from sysarticles where name = @article and pubid = @pubid
select @dest_owner = case when (@dest_owner IS NULL) then N''
else quotename(@dest_owner) + N'.' end
exec sp_MSget_qualified_name @objid, @qualname OUTPUT
-- check if this article has non PK unique keys
exec @fhasnonpkuniquekeys = sp_repltablehasnonpkuniquekey @tabid = @objid
-- start scripting
select @cmd = N'
' + N'
' + N'--
' + N'-- Now the generation phase
' + N'-- Use the conflict case value to decide what to do
' + N'--
' + N'
'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
if (@execution_mode = @QPubWins)
begin
' + N'
' + N'-- Publisher wins resolution
' + N'
'
insert into #proctext(procedure_text) values( @cmd )
-- declare fetch variables for cursor
exec @rc = sp_scriptpubwinsrefreshcursorvars @objid
-- continue scripting
select @cmd = N'
' + N'
' + N'--
' + N'-- Perform single row delete generations first
' + N'--
' + N'
'
insert into #proctext(procedure_text) values( @cmd )
select @cmd = N'
' + N'
' + N'-- Generate DELETE for PK = OLD_PK
' + N'
if (@cftcase in (11,12,13,16,18,21,22,23,24))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating cmd with OLD_PK
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec sp_MSscript_where_clause @objid, @artid, 'qcft_comp', NULL, 0, 'del'
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- continue scripting
select @cmd = N'
end
' + N'
' + N'-- Generate DELETE for PK = NEW_PK
' + N'
if (@cftcase in (1,12,14))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating cmd with NEW_PK
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec sp_MSscript_where_clause @objid, @artid, 'qcft_comp', NULL, 0, 'ins'
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- continue scripting
select @cmd = N'
end'
insert into #proctext(procedure_text) values( @cmd )
-- this scripting is specific to non PK unique keys
if (@fhasnonpkuniquekeys = 1)
begin
-- continue scripting
select @cmd = N'
'+N'
'+N'-- Generate delete for OLD values of non PK keys
'+N'
if (@cftcase in (17,18,23,24))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for OLD values of non PK unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = N'_old'
,@mode = 5
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- continue scripting
select @cmd = N'
end
'+N'
'+N'-- Generate delete for NEW values of non PK keys
'+N'
if (@cftcase in (10,18,21,24))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for NEW values of non PK unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = NULL
,@mode = 5
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- continue scripting
select @cmd = N'
end
' + N'
' + N'--
' + N'-- Perform refresh(delete+insert) generations next
' + N'--
' + N'
' + N'
' + N'-- Generate delete+insert for OLD values of non PK keys
' + N'
if (@cftcase in (21,22))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for OLD values of non PK unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = N'_old'
,@mode = 5
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- script the refresh commands for OLD values of non PK unique keys
exec @rc = sp_MSscript_compensating_insert @publication, @article, @objid, 2, 0
if (@rc != 0 or @@error != 0)
return 1
-- continue scripting
select @cmd = N'
end
' + N'
' + N'-- Generate delete+insert for NEW values of non PK keys
' + N'
if (@cftcase in (20,22,23))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for NEW values of non PK unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = NULL
,@mode = 5
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- script the refresh commands for NEW values of non PK unique keys
exec @rc = sp_MSscript_compensating_insert @publication, @article, @objid, 3, 0
if (@rc != 0 or @@error != 0)
return 1
-- continue scripting
select @cmd = N'
end'
insert into #proctext(procedure_text) values( @cmd )
end
-- continue scripting
select @cmd = N'
' + N'
' + N'-- Generate delete+insert for OLD values of all keys
' + N'
if (@cftcase in (1,3,10,14,15,20))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for OLD values of all unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = N'_old'
,@mode = 4
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- script the refresh commands for OLD values of all unique keys
exec @rc = sp_MSscript_compensating_insert @publication, @article, @objid, 4, 0
if (@rc != 0 or @@error != 0)
return 1
-- continue scripting
select @cmd = N'
end
' + N'
' + N'-- Generate delete+insert for NEW values of all keys
' + N'
if (@cftcase in (15,16,17))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- generate delete compensating command for NEW values of all unique keys
select @cmd = N'
select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
+ sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
insert into #proctext(procedure_text) values( @cmd )
exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
,@artid = @artid
,@prefix = N'@c'
,@suffix = NULL
,@mode = 4
-- script the sending command
exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
-- script the refresh commands for NEW values of all unique keys
exec @rc = sp_MSscript_compensating_insert @publication, @article, @objid, 5, 0
if (@rc != 0 or @@error != 0)
return 1
-- continue scripting
select @cmd = N'
end
' + N'
' + N'--
' + N'-- Perform single row insert generations next
' + N'--
' + N'
' + N'
' + N'-- Generate INSERT for PK = OLD_PK
' + N'
if (@cftcase in (11,18))
begin'
insert into #proctext(procedure_text) values( @cmd )
-- script compensating insert with OLD_PK
exec sp_MSscript_compensating_insert @publication, @article, @objid, 1, 0
-- continue scripting
select @cmd = N'
end
' + N'
' + N'--
' + N'-- all done for conflict resolution for Publisher Wins policy
' + N'--
' + N'
end'
insert into #proctext(procedure_text) values( @cmd )
-- all done
return 0
end