Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscriptupdateconflictfinder

  No additional text.


Syntax
create procedure sys.sp_MSscriptupdateconflictfinder
(
	@publication sysname		-- publication name
	,@article sysname			-- article name
	,@objid int				-- object id
)
as
begin
	declare
			@rc int
			,@cmd nvarchar(4000)
			,@artid int
			,@pubid int
			,@qualname nvarchar(517)
			,@fhasnonpkuniquekeys int

	
	-- initialize the vars we will use
	
	select @pubid = pubid
	from syspublications
	where name = @publication
	select @artid = artid
	from sysarticles
	where name = @article
		and pubid = @pubid
	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'-- This is the crux of the proc for conflict resolution
	' + N'-- This code block is essentially a state machine
	' + N'-- where we ascertain the state of resolution
	' + N'-- The actions of this resolution varies for the policy
	' + N'-- The comments for each state outline the policy
	' + N'-- specific actions
	' + N'-- 
	' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
	if (@execution_mode in (@QPubWins, @QSubWins))
	begin
		declare @fpkeyupdated int'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- non PK unique keys specific scripting
	
	if (@fhasnonpkuniquekeys = 1)
	begin
		select @cmd = N'
		declare @fnpukeyupdated int'
		insert into #proctext(procedure_text) values( @cmd )
	end
	
	-- continue scripting
	
	select @cmd = N'
		' + N'
		' + N'-- initialize the conflict case
		' + N'
		select @cftcase = 0 '
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script the PK update check
	
	select @cmd = N'
		exec @fpkeyupdated = sp_MSispkupdateinconflict ' +
		cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
		if (@fpkeyupdated = -1)
			return -1'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script non PK unique key update check
	
	if (@fhasnonpkuniquekeys = 1)
	begin
		select @cmd = N'
		exec @fnpukeyupdated = sp_MSisnonpkukupdateinconflict ' +
		cast(@pubid as nvarchar(10)) + N', ' + cast(@artid as nvarchar(10)) + N', @bitmap'
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
		if (@fnpukeyupdated = -1)
			return -1'
		insert into #proctext(procedure_text) values( @cmd )
	end
	
	-- continue scripting
	
	select @cmd = N'
		if (@rowcount = 0)
		begin
			' + N'
			' + N'-- we had conflict for this command
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	if (@fhasnonpkuniquekeys = 1)
	begin
		select @cmd = N'
			if (@error in (547, 2601, 2627) or (@fpkeyupdated = 1) or (@fnpukeyupdated = 1)) '
	end
	else
	begin
		select @cmd = N'
			if (@error in (547, 2601, 2627) or (@fpkeyupdated = 1)) '
	end
	insert into #proctext(procedure_text) values( @cmd )
	
	-- continue scripting
	
	select @cmd = N'
			begin
				' + N'
				' + N'-- Conflict due to unique key/constraint
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
				if (@fpkeyupdated = 1)
				begin
					' + N'
					' + N'-- PK is being updated
					' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script check for rows with all keys with OLD values
	
	--if (row exists with pk = OLD_PK or non PK unique keys = OLD values)
	select @cmd = N'
					if exists (select * from ' + @qualname
	insert into #proctext(procedure_text) values( @cmd )
	exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
				,@artid = @artid
				,@prefix = N'@c'
				,@suffix = N'_old'
				,@mode = 6
	insert into #proctext(procedure_text) values( N' )')
	
	-- continue scripting
	
	select @cmd = N'
					begin
						' + N'
						' + N'-- case 14: row(s) with OLD key values exist(s)
						' + N'-- (and rows with NEW key values do not exist)
						' + N'-- PubWins 
						' + N'-- generate delete + insert compensating action with OLD values for all unique keys
						' + N'-- generate delete compensating action for row with PK = NEW_PK
						' + N'-- SubWins 
						' + N'-- delete row with PK=OLD_PK
						' + N'-- insert row with NEW values (use bitmap)
						' + N'
						select @cftcase = 14
					end'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script check for rows with all keys with NEW values (use bitmap)
	
	--if (row exists with pk = NEW_PK or non PK unique keys = NEW values)
	select @cmd = N'
					if exists (select * from ' + @qualname
	insert into #proctext(procedure_text) values( @cmd )
	exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
				,@artid = @artid
				,@prefix = N'@c'
				,@suffix = NULL
				,@mode = 6
	insert into #proctext(procedure_text) values( N' )')
	
	-- continue scripting
	
	select @cmd = N'
					begin
						' + N'
						' + N'-- row with NEW key values exist(s)
						' + N'
						if (@cftcase = 14)
						begin
							' + N'
							' + N'-- case 15: rows exist with NEW key values and OLD key values
							' + N'-- PubWins 
							' + N'-- generate delete + insert compensating action with OLD values for all unique keys
							' + N'-- generate delete + insert compensating action with NEW values for all unique keys
							' + N'-- SubWins 
							' + N'-- delete row with PK=OLD_PK
							' + N'-- delete rows with NEW values of all keys
							' + N'-- insert row with NEW values (use bitmap)
							' + N'
							select @cftcase = 15
						end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
						else
						begin
							' + N'
							' + N'-- case 16: rows exist with NEW key values and
							' + N'-- row does not exist for OLD values
							' + N'-- PubWins 
							' + N'-- generate delete compensating action for row with PK = OLD_PK
							' + N'-- generate delete + insert compensating action with NEW values for all unique keys
							' + N'-- SubWins 
							' + N'-- delete rows with NEW values of all keys
							' + N'-- insert row with NEW values (use bitmap)
							' + N'
							select @cftcase = 16
						end
					end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
					else
					begin
						' + N'
						' + N'-- row with NEW key values does not exist
						' + N'
						if (@cftcase = 0)
						begin
							' + N'
							' + N'-- case 12 : no existing rows with OLD key values or NEW or new key values
							' + N'-- PubWins 
							' + N'-- generate delete compensating action with PK = OLD_PK
							' + N'-- generate delete compensating action with PK = NEW_PK
							' + N'-- SubWins 
							' + N'-- insert row with NEW values (use bitmap)
							' + N'
							select @cftcase = 12
						end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
						else
						begin
							' + N'
							' + N'-- case 14: row(s) with OLD key values exist(s)
							' + N'-- (and rows with NEW key values do not exist)
							' + N'-- PubWins 
							' + N'-- generate delete + insert compensating action with OLD values for all unique keys
							' + N'-- generate delete compensating action for row with PK = NEW_PK
							' + N'-- SubWins 
							' + N'-- delete row with PK=OLD_PK
							' + N'-- insert row with NEW values (use bitmap)
							' + N'
							select @cftcase = 14
						end
					end
				end'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script this block if the article has non PK unique keys
	
	if (@fhasnonpkuniquekeys = 1)
	begin
		
		-- continue scripting
		
		select @cmd = N'
				else if (@fnpukeyupdated = 1)
				begin
					'+N'
					'+N'-- non PK unique keys are being updated but PK is not updated
					'+N'-- OLD_PK == NEW_PK in these cases
					'+N'--'
		insert into #proctext(procedure_text) values( @cmd )
		
		-- script the pkrowexists assignment
		
		select @cmd = N'
					declare @pkrowexist bit
					
					if exists (select * from ' + @qualname
		insert into #proctext(procedure_text) values( @cmd )
		exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0
		select @cmd =  N' )
						select @pkrowexist = 1 '
		insert into #proctext(procedure_text) values( @cmd )
		
		-- script check for rows with non PK keys with OLD values
		
		-- if (rows exist with OLD values of non PK unique keys values)
		select @cmd = N'
					if exists (select * from ' + @qualname
		insert into #proctext(procedure_text) values( @cmd )
		exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
					,@artid = @artid
					,@prefix = N'@c'
					,@suffix = N'_old'
					,@mode = 7
		insert into #proctext(procedure_text) values( N' )')
		
		-- continue scripting
		
		select @cmd = N'
					begin
						if (@pkrowexist = 1)
						begin
							'+N'
							'+N'-- case 10: rows exist with OLD non PK key values
							'+N'-- (and rows with NEW non PK key values do not exist)
							'+N'-- and row with OLD_PK exists
							'+N'-- PubWins 
							'+N'-- generate delete + insert compensation action with OLD values for all keys
							'+N'-- generate delete compensating action with NEW values for non PK keys
							'+N'-- SubWins 
							'+N'-- delete row with PK=OLD_PK
							'+N'-- insert row with NEW values (use bitmap)
							'+N'
							select @cftcase = 10
						end'
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
						else
						begin
							'+N'
							'+N'-- case 21: rows exist with OLD non PK key values
							'+N'-- (and rows with NEW non PK key values do not exist)
							'+N'-- and row with OLD_PK does not exist
							'+N'-- PubWins 
							'+N'-- generate delete with PK = OLD_PK
							'+N'-- generate delete + insert compensation action with OLD values for non PK keys
							'+N'-- generate delete compensating action with NEW values for non PK keys
							'+N'-- SubWins 
							'+N'-- delete row with PK=OLD_PK
							'+N'-- insert row with NEW values (use bitmap)
							'+N'
							select @cftcase = 21
						end
					end '
		insert into #proctext(procedure_text) values( @cmd )
		
		-- script check for rows with non PK keys with NEW values (use bitmap)
		
		-- if (rows exist with NEW values of non PK unique keys values)
		select @cmd = N'
					if exists (select * from ' + @qualname
		insert into #proctext(procedure_text) values( @cmd )
		exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
					,@artid = @artid
					,@prefix = N'@c'
					,@suffix = NULL
					,@mode = 7
		insert into #proctext(procedure_text) values( N' )')
		
		-- continue scripting
		
		select @cmd = N'
					begin
						'+N'
						'+N'-- find the type of conflict
						'+N'
						if (@cftcase in (10,21))
						begin
							if (@pkrowexist = 1)
							begin
								'+N'
								'+N'-- case 20: rows exist with OLD and NEW values of non PK keys
								'+N'-- and row with OLD_PK exists
								'+N'-- PubWins 
								'+N'-- generate delete + insert compensation action with OLD values for all keys
								'+N'-- generate delete + insert compensation action with NEW values for non PK keys
								'+N'-- SubWins 
								'+N'-- delete row with PK=OLD_PK
								'+N'-- delete row with NEW values for non PK keys
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 20
							end '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
							else
							begin
								'+N'
								'+N'-- case 22: rows exist with OLD and NEW values of non PK keys
								'+N'-- and row with OLD_PK does not exist
								'+N'-- PubWins 
								'+N'-- generate delete with PK = OLD_PK
								'+N'-- generate delete + insert compensation action with OLD values for non PK keys
								'+N'-- generate delete + insert compensation action with NEW values for non PK keys
								'+N'-- SubWins 
								'+N'-- delete row with PK=OLD_PK
								'+N'-- delete row with NEW values for non PK keys
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 22
							end
						end '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
						else
						begin
							if (@pkrowexist = 1)
							begin
								'+N'
								'+N'-- case 17: rows exist with NEW values of non PK keys
								'+N'-- and row does not exist with OLD values of non PK keys
								'+N'-- and row with OLD_PK exists
								'+N'-- PubWins 
								'+N'-- generate delete compensating action with OLD values for non PK keys
								'+N'-- generate delete + insert compensation action with NEW values for all keys
								'+N'-- SubWins 
								'+N'-- delete row with NEW values for non PK keys
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 17
							end '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
							else
							begin
								'+N'
								'+N'-- case 23: rows exist with NEW values of non PK keys
								'+N'-- and row does not exist with OLD values of non PK keys
								'+N'-- and row with OLD_PK does not exist
								'+N'-- PubWins 
								'+N'-- delete row with PK=OLD_PK
								'+N'-- generate delete compensating action with OLD values for non PK keys
								'+N'-- generate delete + insert compensation action with NEW values for non PK keys
								'+N'-- SubWins 
								'+N'-- delete row with NEW values for non PK keys
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 23
							end
						end
					end '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
					else
					begin
						'+N'
						'+N'-- row does not exist with NEW values of non PK keys
						'+N'
						if (@cftcase = 0)
						begin
							if (@pkrowexist = 1)
							begin
								'+N'
								'+N'-- case 18 : no existing rows with OLD or NEW values of non PK keys
								'+N'-- and row with OLD_PK exists
								'+N'-- PubWins 
								'+N'-- delete row with PK=OLD_PK
								'+N'-- generate delete compensating action with OLD values for non PK keys
								'+N'-- generate delete compensating action with NEW values for non PK keys
								'+N'-- generate insert with PK = OLD_PK
								'+N'-- SubWins 
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 18
							end '
		insert into #proctext(procedure_text) values( @cmd )
		select @cmd = N'
							else
							begin
								'+N'
								'+N'-- case 24 : no existing rows with OLD or NEW values of non PK keys
								'+N'-- and row with OLD_PK does not exist
								'+N'-- PubWins 
								'+N'-- delete row with PK=OLD_PK
								'+N'-- generate delete compensating action with OLD values for non PK keys
								'+N'-- generate delete compensating action with NEW values for non PK keys
								'+N'-- SubWins 
								'+N'-- insert row with NEW values (use bitmap)
								'+N'
								select @cftcase = 24
							end
						end
					end
				end '
		insert into #proctext(procedure_text) values( @cmd )
	end
	
	-- continue scripting
	
	select @cmd = N'
			end
			else
			begin
				' + N'
				' + N'-- Conflict due non key column change or row deleted
				' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- script check for rows with pk = OLD_PK
	
	select @cmd = N'
				if exists (select * from ' + @qualname
	insert into #proctext(procedure_text) values( @cmd )
	exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0
	insert into #proctext(procedure_text) values( N' )')
	
	-- continue scripting
	
	select @cmd = N'
				begin
					' + N'
					' + N'-- case 11: row exists
					' + N'-- PubWins 
					' + N'-- generate delete + insert compensating action with PK = OLD_PK
					' + N'-- SubWins 
					' + N'-- delete row with PK=OLD_PK
					' + N'-- insert row with NEW values (use bitmap)
					' + N'
					select @cftcase = 11
				end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
				else
				begin
					' + N'
					' + N'-- case 13: row does not exist
					' + N'-- PubWins 
					' + N'-- generate delete compensating action with PK = OLD_PK
					' + N'-- SubWins 
					' + N'-- insert row with NEW values (use bitmap)
					' + N'
					select @cftcase = 13
				end
			end
		end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
		else if (@execution_mode = @QPubWins)
		begin
			' + N'
			' + N'-- we had no conflict for this command
			' + N'-- We need to process this block only in the Publisher Wins cases
			' + N'--'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
			if (@fpkeyupdated = 1)
			begin
				' + N'
				' + N'-- PK is being updated
				' + N'-- PubWins 
				' + N'-- generate delete + insert compensating action with OLD values for all unique keys
				' + N'-- generate delete compensating action with PK=NEW_PK
				' + N'
				select @cftcase = 1
			end'
	insert into #proctext(procedure_text) values( @cmd )
	select @cmd = N'
			else
			begin
				' + N'
				' + N'-- non PK column updated
				' + N'-- PubWins 
				' + N'-- generate delete + insert compensating action with OLD values for all unique keys
				' + N'
				select @cftcase = 3
			end
		end
	end'
	insert into #proctext(procedure_text) values( @cmd )
	
	-- all done
	
	return 0	
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscript_sync_upd_proc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash