Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmakepeerconflicttable

  No additional text.


Syntax
create procedure sys.sp_MSmakepeerconflicttable (
	@fulltablename nvarchar(540), --qualified with owner
	@schema_option  bigint,
	@scriptonly bit = 0
)
as
begin

    	declare	@retcode        int
    			,@basetabid		int
			,@ownername     nvarchar(1000)
			,@basetablename     nvarchar(1000)
			,@conflicttablename nvarchar(1000)
			,@indexname nvarchar(1000)
			,@cmd           nvarchar(max)
			,@colid     int
			,@colname       nvarchar(1000)
			,@coltype       nvarchar(1000)
			,@indid         int
			,@indkey        int
			,@key           nvarchar(1000)
			,@dbname	nvarchar(1000)
			,@tmptablecreated bit = 0
			
	set nocount on

	-- Check and make sure the table exists
	select @basetabid = OBJECT_ID (@fulltablename, N'U')
	if @basetabid is null
	begin
		raiserror(15388, 16, 1, @fulltablename)
		return (1)
	end

	select	@basetablename = OBJECT_NAME(@basetabid)
			,@ownername = schema_name(objectproperty(@basetabid, 'SchemaId'))

	-- generate the conflict table name
	exec sp_MSgetpeerconflictname @prefix = N'conflict', @tabid = @basetabid, @peerconflictname = @conflicttablename output
	if @@error <> 0 or @conflicttablename is NULL
		return (1)

	select @conflicttablename = N'[dbo].' + QUOTENAME(@conflicttablename)

	-- generate the index name
	exec sp_MSgetpeerconflictname @prefix = N'cftind', @tabid = @basetabid, @peerconflictname = @indexname output

	-- begin tran
	begin tran
	save tran tr_sp_MSmakepeerconflicttable

	if OBJECT_ID(@conflicttablename, N'U') is not null
	begin
		if exists(select * from sys.objects where object_id = OBJECT_ID(@conflicttablename, N'U') and is_ms_shipped = 1)
		begin --assume it IS the conflict table, if it is marked as MS shipped
			goto COMMIT_TRAN
		end
		else
		begin
			select @cmd = N'DROP TABLE ' + @conflicttablename
			exec (@cmd) --existing table with the same name is dropped
			if @@error <> 0
			begin
				goto UNDO
			end
			else
			begin
				raiserror(22809, 10, -1, @conflicttablename) --raise a warning
			end
		end
	end
	

	exec @indid = sys.sp_MStable_has_unique_index @basetabid
	if (@indid = 0)
	begin
		raiserror(21750, 16, 1, @fulltablename)
				goto UNDO
	end

	if  (OBJECT_ID(N'tempdb..#tempcmd', N'U') IS NOT NULL)
		drop table #tempcmd
		
	create table #tempcmd (step int identity NOT NULL, cmdtext nvarchar(max) NULL)
	if @@error <> 0
		goto UNDO
	else
		select @tmptablecreated = 1

	insert into #tempcmd(cmdtext) values(N'CREATE TABLE ' + @conflicttablename + N' (')
	insert into #tempcmd(cmdtext) values(N'__$originator_id int NULL')
	insert into #tempcmd(cmdtext) values(N',__$origin_datasource int NULL')
	insert into #tempcmd(cmdtext) values(N',__$tranid nvarchar(40) NULL')
	insert into #tempcmd(cmdtext) values(N',__$conflict_type int NULL')
	insert into #tempcmd(cmdtext) values(N',__$is_winner bit NULL')
	insert into #tempcmd(cmdtext) values(N',__$pre_version varbinary(32) NULL')
	insert into #tempcmd(cmdtext) values(N',__$reason_code int NULL')
	insert into #tempcmd(cmdtext) values(N',__$reason_text nvarchar(720) NULL')
	insert into #tempcmd(cmdtext) values(N',__$update_bitmap varbinary(32) NULL')
	insert into #tempcmd(cmdtext) values(N',__$inserted_date datetime DEFAULT GETDATE ( ) NOT NULL ')
	insert into #tempcmd(cmdtext) values(N',__$row_id rowversion NOT NULL')
	insert into #tempcmd(cmdtext) values(N',__$change_id binary(8) NULL')


	-- Declare the cursor to get info on each column of base table
	declare #hcurColumnInfo cursor local FAST_FORWARD FOR
		select column_id, name
		from sys.columns
		where is_computed = 0 	and object_id = @basetabid
		order by column_id
	for read ONLY

	open #hcurColumnInfo
	fetch #hcurColumnInfo into @colid, @colname
	while (@@FETCH_STATUS = 0)
	begin

		-- Get the typestring for this column
		-- Skip this column if it is NULL

		select @coltype = [sys].[fn_gettypestring](@basetabid, @colid, 1, 0, 0, 0, 1, 0, 0, 0, 0)
		if (@@ERROR != 0 or @coltype is NULL)
		begin
			raiserror(21542, 16, 1, @@error, 'fn_gettypestring')
			goto UNDO
		end

		-- Create the column info, always allow NULL regardless of source, it's always possible to have to insert NULL in some of the columns in case update/delete is not using CALL/XCALL format
		insert into #tempcmd(cmdtext) values(N',' + quotename(@colname) + N' ' + @coltype + N' NULL')

		fetch #hcurColumnInfo into @colid, @colname
	end
	-- insert right )  into the temptable
	insert into #tempcmd(cmdtext) values(N')')
	close #hcurColumnInfo
	deallocate #hcurColumnInfo

	-- Create an unique index - we add some three fields to the index of base table
	insert into #tempcmd(cmdtext) values(N'
						CREATE UNIQUE INDEX ' + quotename(@indexname) + N' ON ' +  @conflicttablename  + N'( __$originator_id ')

	select @indkey = 1
			,@cmd = N''
	while (@indkey <= 16)
	begin
		select @key = index_col(@fulltablename, @indid, @indkey)
		if (@key is not null)
		begin
			select @cmd = @cmd + N', ' + quotename(@key)
		end
		select @indkey = @indkey + 1
	end

	
	-- Add two more fields in the end for the index
	
	select @cmd = @cmd + N', __$tranid, __$row_id)'
	insert into #tempcmd(cmdtext) values(@cmd)
	
	if (@scriptonly = 1)
	begin
		select cmdtext from #tempcmd order by step
	end
	else
	begin
		
		-- create the table now
		
		select @dbname = db_name()
		select @cmd = 'select cmdtext from #tempcmd order by step'
		exec @retcode = sys.xp_execresultset @cmd, @dbname
		if (@@error != 0 or @retcode != 0)
		begin
			raiserror(21542, 16, 1, @@error, 'xp_execresultset')
			goto UNDO
		end

		exec @retcode = dbo.sp_MS_marksystemobject @conflicttablename
		if (@@error != 0 or @retcode != 0)
		begin
			-- roll back the tran
			raiserror(21542, 16, 1, @@error, 'sp_MS_marksystemobject')
			goto UNDO
		end

	end

COMMIT_TRAN:
	
	-- commit the tran
	
	if @tmptablecreated = 1
		drop table #tempcmd
	commit tran tr_sp_MSmakepeerconflicttable
	return (0)

UNDO:
	rollback tran tr_sp_MSmakepeerconflicttable
	commit tran
	return (1)
end

 
Last revision 2008RTM
See also

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