Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_peerconflictdetection_tableaug

  No additional text.


Syntax
create procedure sys.sp_peerconflictdetection_tableaug (
	@publisher sysname,
	@publisher_db sysname,
	@publication	sysname, -- not null
	@enabling	bit, -- not null
	@originator_id int, --should not be null and not 0 and not 0x80000000, when @enabling is 1 and when the destination is a peer
	@artlist 		nvarchar(max) --in xml format
)
as
begin
	set nocount on
	
	declare	@retcode int = 0,
			@tabrepobjsexists bit,
			@OPT_ENABLED_FOR_P2P int = 0x1,
			@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int = 0x8,
			@ispub bit, --used only when disabling
			@cursor_allocated bit = 0,
			@cursor_opened bit = 0,
			@schname sysname,
			@tabname sysname,
			@artname sysname,
			@schema_option bigint,
			@fulltablename nvarchar(540), --qualified with owner
			@tabobjid int,
			@conftabname nvarchar(540),
			@forcetabaug bit  --used only when disabling

	-- security check - should be dbo or sysadmin
	if (@originator_id = 0x80000000) --read-only subscriber
	begin
		exec @retcode = sp_MSreplcheck_subscribe
		if @@ERROR != 0 or @retcode != 0
			return (1)
	end
	else
	begin
		exec @retcode = sp_MSreplcheck_publish
		if @@ERROR != 0 or @retcode != 0
			return (1)
	end

	-- obtain article list
	declare @arttab table (schname sysname,  tabname sysname,  artname sysname,  schema_option bigint)
	declare @DocHandle int

	begin tran
	save tran tr_tableaug
	
	exec sp_xml_preparedocument @DocHandle OUTPUT, @artlist
	if @@error <> 0
		goto UNDO
	
	insert @arttab
		select  * 	
		from OPENXML (@DocHandle, N'/arts/art', 1)
		with (schname sysname,  tabname sysname,  artname sysname,  schema_option bigint)
	if @@error <> 0
		goto UNDO
	
	exec sp_xml_removedocument @DocHandle
	if @@error <> 0
		goto UNDO

	if exists (select * from sys.objects where name = N'MSreplication_objects')
		select @tabrepobjsexists = 1
	else
		select @tabrepobjsexists = 0


	if @enabling = 1
	begin --read-only subscription table has its p2porid as 0x80000000
		if not exists (select * from sys.objects where name = N'syspublications')
			select @originator_id = 0x80000000
		else if not exists (select * from syspublications
					where name = @publication
					and options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION)
			select @originator_id = 0x80000000
	end
	else
	begin
		if not exists (select * from sys.objects where name = N'syspublications')
			select @ispub = 0
		else if not exists (select * from syspublications
				where name = @publication
				and options & @OPT_ENABLED_FOR_P2P = @OPT_ENABLED_FOR_P2P)
			select @ispub  = 0
		else
			select @ispub  = 1
	end
	
	--process all articles
	declare #harticle cursor local fast_forward for
	select * from @arttab

	if (@@error != 0)
		goto UNDO

	select @cursor_allocated = 1

	open #harticle
	if (@@error != 0)
		goto UNDO

	select @cursor_opened = 1

	fetch #harticle into @schname, @tabname, @artname, @schema_option
	while (@@fetch_status<>-1)
	begin
		select @fulltablename = QUOTENAME(@schname) + N'.' + QUOTENAME(@tabname)
		select @tabobjid = object_id(@fulltablename)
		if(@tabobjid is not NULL and exists (select * from sys.objects where object_id = @tabobjid and type = N'U'))
		begin
			select @conftabname =sys.fn_peerconflicttablename(@fulltablename)
			
			if @enabling = 1 --create conflict table and insert object name into MSreplication_objects in case of read-only subscriptions
			begin
				if @originator_id <> 0x80000000 --do not create conflict table on a read-only subscriber for now
				begin
					exec @retcode = sp_MSmakepeerconflicttable @fulltablename , @schema_option
					if @@error != 0 or @retcode = 1
						goto UNDO

					--leave the code to insert into MSreplication_objects here
					if @originator_id = 0x80000000 and  @tabrepobjsexists  = 1
					begin
						if not exists (select * from dbo.MSreplication_objects where object_name =@conftabname
																	and publisher = @publisher
																	and publisher_db = @publisher_db
																	and publication = @publication
																	and article = @artname
																	and object_type = N'U')
					  		insert dbo.MSreplication_objects (publisher, publisher_db, publication, object_name, object_type, article)
							values(@publisher, @publisher_db, @publication, @conftabname, N'U', @artname)
					end	
				end

							
				EXEC %%Object(MultiName = @fulltablename).LockMatchID(ID = @tabobjid, Exclusive = 1, BindInternal = 0)
				--enabling: for read-only subscription, @replpeerid is 0x80000000; for peer,  @replpeerid is @originator_id
				--this will internally add hidden column
				exec %%Relation(ID = @tabobjid).SetReplPeerId(Value = @originator_id)
				if @@error != 0
					goto UNDO
			end
			else --enabling = 0
			begin
				if @originator_id = 0
					select @forcetabaug = 1
				else if @ispub = 0 and @tabrepobjsexists = 1
				begin
					delete from dbo.MSreplication_objects where object_name = @conftabname
												and publisher = @publisher
												and publisher_db = @publisher_db
												and publication = @publication
												and article = @artname
												and object_type = N'U'

					if not exists (select * from dbo.MSreplication_objects where object_name = @conftabname
																and object_type = N'U')
						select @forcetabaug = 1
				end
				
				if @forcetabaug = 1
				begin
					-- prefix with scheme name: dbo
					select @conftabname = N'[dbo].' + QUOTENAME(@conftabname)
					if object_id(@conftabname) is not NULL
						exec ('drop table ' + @conftabname)  -- drop conflict table

					EXEC %%Object(MultiName = @fulltablename).LockMatchID(ID = @tabobjid, Exclusive = 1, BindInternal = 0)
					-- disabling: @replpeerid is 0, this will internally drop hidden column
					exec %%Relation(ID = @tabobjid).SetReplPeerId(Value = 0)
					if @@error != 0
						goto UNDO
				end
			end
		end

		fetch #harticle into @schname, @tabname, @artname, @schema_option
	end --while

	if @cursor_opened = 1
	begin
		close #harticle
	end

	if @cursor_allocated = 1
	begin
		deallocate #harticle
	end

	commit tran
	return (0)


UNDO:

	if @cursor_opened = 1
	begin
		close #harticle
	end

	if @cursor_allocated = 1
	begin
		deallocate #harticle
	end

	rollback tran tr_tableaug
	commit tran

	raiserror(21542, 16, 1, @@error, 'sp_peerconflictdetection_tableaug')
	return (1)

end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscriptpeerconflictdetection_tableaug (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