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