May 21, 2012

sp_MSmakeconflictinsertproc (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSmakeconflictinsertproc(nvarchar @tablename
, nvarchar @ownername
, nvarchar @procname
, int @basetableid
, uniqueidentifier @pubid
, bit @generate_subscriber_proc)

MetaData:

   

-- usage pattern from CMergeDatasource should be:
create procedure sys.sp_MSmakeconflictinsertproc
(@tablename sysname, -- conflict table
@ownername sysname,
@procname sysname,
@basetableid int, -- base table the article is from
@pubid uniqueidentifier=NULL,
@generate_subscriber_proc bit = 0
)
as
declare @arglist nvarchar(4000)
declare @header nvarchar(4000)
declare @qualname nvarchar(517) -- 258 * 2 + 1
declare @argname nvarchar(270)
declare @wherepc nvarchar(255)
declare @idstr nvarchar(100)
declare @colname nvarchar(258)
declare @guidcol nvarchar(258)
declare @typename nvarchar(258)
declare @base_typename sysname
declare @schname nvarchar(140) -- use this to track the user schema name
declare @len smallint
declare @prec smallint
declare @scale int
declare @retcode smallint
declare @create_time_col nvarchar(8)
declare @pubidstr nvarchar(40)
declare @guid_colname sysname
declare @original_datasourcecol sysname
declare @tablenick int
declare @tablenickstr nvarchar(12)
declare @basetablename nvarchar(270)
declare @qualifiedbasetable nvarchar(517) -- 258 * 2 + 1
declare @qualifiedbasetable2 nvarchar(517) -- 258 * 2 + 1
declare @basetableowner nvarchar(270)
declare @maintainsmetadata bit
declare @artid uniqueidentifier
declare @schema_option varbinary(8)
declare @user_type_id int

declare @id int -- conflict table id
declare @sync_objid int -- article view

declare @colid smallint -- index in sys.columns, used to iterate through sys.columns
declare @colordinal smallint -- index in @setbm, used to interate actual data sent across
declare @colordstr nvarchar(4) -- @colordinal stringification

declare @noset bit -- set when column is two special ones: rowguidcol or origin_datasource_id.
-- they are discovered when @sys_loop=1 and need to be treated specially for the insertion to conflict table (ie skipped there)
declare @sys_loop bit -- 0 as first staget for columns in base table, 1 as 2nd stage for columns not in base table
declare @quotedprocname nvarchar(258)

set nocount on

set @guidcol='rowguid'

select @sys_loop = 0
set @create_time_col = NULL

if (@ownername is null or @ownername = ' ')
set @qualname = QUOTENAME(@tablename)
else
set @qualname = QUOTENAME(@ownername) + '.' + QUOTENAME(@tablename)

--
-- To check if specified object exists in current database
--
set @id = object_id(@qualname)
if @id is NULL return (1)
set @idstr = rtrim(convert(nchar, @id))
select @pubidstr = case when @pubid is null then 'NULL' else
'''' + convert(nvarchar(40),@pubid) + '''' end

-- security check
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid=@pubid, @objid=@id
if @@error <> 0 or @retcode <> 0
return 1

set @basetablename= object_name(@basetableid)
select @basetableowner= SCHEMA_NAME(schema_id) from sys.objects where object_id=@basetableid

if @basetableowner is not null
set @qualifiedbasetable= quotename(@basetableowner) + '.' + quotename(@basetablename)
else
set @qualifiedbasetable= quotename(@basetablename)

if @generate_subscriber_proc = 1
begin
select @qualifiedbasetable2 = quotename(destination_owner) + '.' + quotename(@basetablename) from dbo.sysmergearticles where pubid=@pubid and objid = object_id(@qualifiedbasetable)
end
else
set @qualifiedbasetable2= @qualifiedbasetable

execute @retcode= sys.sp_MStablenickname @basetableowner, @basetablename, @tablenick output
if @@ERROR <>0 OR @retcode <>0 return (1)
set @tablenickstr = rtrim(convert(nchar, @tablenick))

-- create temp table to select the command text out of
create table #tempcmd (phase int NOT NULL, step int identity NOT NULL,
cmdtext nvarchar(max) collate database_default null)

-- create temp table that will be used by sp_MScreatedupkeyupdatequery
create table #coltab (colname nvarchar(140), paramname nvarchar(10))

-- now create the procedure
select @quotedprocname = QUOTENAME(@procname)

if @generate_subscriber_proc = 0
begin
exec ('if object_id (''dbo.' + @quotedprocname + ''') is not null drop procedure dbo.' + @quotedprocname)
if @@error<>0 return 1
end

select @header = 'Create procedure dbo.' + @quotedprocname + ' ( '
insert into #tempcmd (phase, cmdtext) values (0, @header)

select @sync_objid= sync_objid, @artid= artid, @schema_option = schema_option from dbo.sysmergearticles where objid = @basetableid and (pubid = @pubid or @pubid is NULL)

set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default)

-- it doesn't make sense to replicate only token column - rowguidcol column
declare @colcount int
declare @column sysname

select @colcount=count(*) from sys.columns where object_id = @sync_objid
if @colcount = 1
begin
select @column=name from sys.columns where object_id = @sync_objid
if ColumnProperty(@id, @column, 'isrowguidcol') = 1
begin
raiserror(21597, 16, -1)
return (1)
end
end

set @colordinal=0

-- the column exists in conflict table and
-- either exists or not exists
-- in base table
if @sys_loop =1
select @colid = min(column_id) from sys.columns where object_id = @id and is_computed<>1 and user_type_id <> type_id('timestamp')
and name not in (select name from sys.columns where object_id=@basetableid)
else
select @colid = min(column_id) from sys.columns where object_id = @id and is_computed<>1 and user_type_id <> type_id('timestamp')
and name in (select name from sys.columns where object_id=@basetableid)

select top 1 @colname = C.name,
@typename = type_name(C.user_type_id),
@base_typename=type_name(C.system_type_id),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.'
else '' end,
@prec = C.precision,
@scale = C.scale,
@user_type_id = C.user_type_id
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @id and C.column_id = @colid

--
-- Get the column list from the conflict_table schema and filter it with
table view for vertical partitioning
--
-- do first for basic columns and 2nd for columns not in basic tables
Reverse_Order:
-- loop over columns
while (@colname is not null)
begin
set @noset = 0
-- skip unless
-- the column exists in base table and in article view
-- or the column does not exist in base table (i.e. conflict table only)
if exists (select * from sys.columns where name=@colname and object_id=@basetableid)
and not exists (select * from sys.columns where name=@colname and object_id=@sync_objid)
goto NEXT_COL

set @colordinal = @colordinal + 1
set @colordstr = convert(nvarchar(4), @colordinal)

if ((@base_typename='nvarchar' or @base_typename='nchar') and @len <> -1)-- a unit of nchar takes 2 bytes
select @len = @len/2

if @generate_subscriber_proc = 0
exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
else
exec @retcode = sys.sp_MSmap_subscriber_type @user_type_id, @len, @prec, @scale, @schema_option, @typename out, @schname out
if @@ERROR<>0 or @retcode<>0 return (1)
select @argname = '@p' + rtrim(convert(nchar, @colid))

insert into #coltab (colname, paramname) values (quotename(@colname), @argname)

-- based on colid, add text to appropriate pieces
if (COLUMNPROPERTY( @basetableid, @colname, 'IsRowGuidCol') = 1)
begin
select @noset =1
select @guid_colname=@argname -- @px
set @wherepc = ' where info.rowguid = ' + @argname
set @guidcol=QUOTENAME(@colname) -- rowguid or [rg] like
end
else if (@colname = 'origin_datasource_id')
begin
select @wherepc =@wherepc + ' and info.origin_datasource = ' + @argname
select @typename = ' nvarchar(255) '
select @original_datasourcecol = @argname; -- @px
set @noset =1
end
set @colname = QUOTENAME(@colname)

-- 1st piece
if @arglist is null
begin
set @arglist = @argname + ' ' + @schname + @typename
-- give default value of NULL to new merge columns for backward compatibility concern
insert into #tempcmd (phase, cmdtext) values (3, @colname)
select @header = ') values ('
insert into #tempcmd (phase, cmdtext) values (4, @header)
insert into #tempcmd (phase, cmdtext) values (4, nchar(13)+nchar(10)+@argname)
-- this is update case
if @noset=0
begin
insert into #tempcmd (phase, cmdtext) values (1, @colname + ' = ' + @argname)
end
end
-- consecutive piece
else
begin
if len(@arglist)>3700
begin
insert into #tempcmd (phase, cmdtext) values (0, @arglist)
select @arglist = ' '
end
set @arglist = @arglist + ',
'
+ @argname + ' ' + @schname + @typename

-- 3 and 4 are for insert part
insert into #tempcmd (phase, cmdtext) values (3, ',
'
+ @colname)

if (@argname = @original_datasourcecol)
select @argname=' @source_id '
insert into #tempcmd (phase, cmdtext) values (4, ',
'
+ @argname) -- source id case

-- 1 is for update part
if @noset =0
begin
if exists (select * from #tempcmd where phase=1)
insert into #tempcmd (phase, cmdtext) values (1, ',
'
+ @colname + ' = ' + @argname)
else
insert into #tempcmd (phase, cmdtext) values (1, @colname + ' = ' + @argname)
end
end
NEXT_COL:
if @sys_loop =1
select @colid = min(column_id) from sys.columns where object_id = @id and is_computed<>1 and user_type_id <> type_id('timestamp')
and name not in (select name from sys.columns where object_id=@basetableid)
and column_id>@colid
else
select @colid = min(column_id) from sys.columns where object_id = @id and is_computed<>1 and user_type_id <> type_id('timestamp')
and name in (select name from sys.columns where object_id=@basetableid)
and column_id>@colid

set @colname = NULL

select top 1 @colname = C.name,
@typename = type_name(C.user_type_id),
@base_typename = type_name(C.system_type_id),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@prec = C.precision,
@scale = C.scale,
@user_type_id = C.user_type_id
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @id and C.column_id = @colid

end

if @sys_loop = 0
begin
select @sys_loop = 1
select @colid = min(column_id) from sys.columns where object_id = @id and is_computed<>1 and user_type_id <> type_id('timestamp')
and name not in (select name from sys.columns where object_id=@basetableid)

select top 1 @colname = C.name,
@typename = type_name(C.user_type_id),
@base_typename = type_name(C.system_type_id),
@len = C.max_length,
@schname=case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
@prec = C.precision,
@scale = C.scale,
@user_type_id = C.user_type_id
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @id and C.column_id = @colid

goto Reverse_Order
end

insert into #tempcmd (phase, cmdtext) values (0, @arglist)
select @arglist = ', @conflict_type int, @reason_code int, @reason_text nvarchar(720)'
insert into #tempcmd (phase, cmdtext) values (0, @arglist)
select @arglist = ', @pubid uniqueidentifier, @create_time datetime = NULL'
insert into #tempcmd (phase, cmdtext) values (0, @arglist)
select @arglist = ', @tablenick int = 0, @source_id uniqueidentifier = NULL, @check_conflicttable_existence bit = 0 '
insert into #tempcmd (phase, cmdtext) values (0, @arglist)

select @header = ') as
declare @retcode int
-- security check
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @objid = '
+ @idstr + ', @pubid = ' + @pubidstr + '
if @@error <> 0 or @retcode <> 0 return 1 '

insert into #tempcmd (phase, cmdtext) values (0, @header)

-- If the subscriber database was upgraded to Yukon, and we now use conflict logging on both sides
-- the subscriber database might not have the conflict logging table.
-- We then return without error.
set @header='
if 1 = @check_conflicttable_existence
begin
if '
+ cast(@id as nvarchar) + ' is null return 0
end
'

insert into #tempcmd (phase, cmdtext) values (0, @header)

-- select @header = ' if @source_id is NULL select @source_id = newid() '
select @header = '
if @source_id is NULL
select @source_id = subid from dbo.sysmergesubscriptions
where lower('
+ @original_datasourcecol + ') = LOWER(subscriber_server) + ''.'' + LOWER(db_name) '

insert into #tempcmd (phase, cmdtext) values (0, @header)

-- For down level anonymous pull subscriptions, the Publisher's sysmergesubscriptions does not contain a valid entry for the Subscriber
-- Hence the source_id might still be NULL -In that case generate a new guid

select @header = '
if @source_id is NULL select @source_id = newid() '

insert into #tempcmd (phase, cmdtext) values (0, @header)

select @header = '
set @create_time=getdate()
'

insert into #tempcmd (phase, cmdtext) values (0, @header)

select @header = ' '
-- for ease of expansion here in case we add new merge columns in conflict tables.
if @create_time_col is not NULL
select @header = @header + '
select '
+ @create_time_col + ' = getdate() '

select @header = @header + ' if exists (select * from MSmerge_conflicts_info info inner join ' + @qualname + ' ct
on ct.rowguidcol=info.rowguid and
ct.origin_datasource_id = info.origin_datasource_id
'
+ @wherepc + ' and info.tablenick = @tablenick)
begin
update '
+ @qualname + ' with (rowlock) set '
insert into #tempcmd (phase, cmdtext) values (0, @header)

-- see comment in sp_MSinsertdeleteconflict for this <5 or >4 checking.

select @header = ' from ' + @qualname + ' ct inner join MSmerge_conflicts_info info
on ct.rowguidcol=info.rowguid and
ct.origin_datasource_id = info.origin_datasource_id'

insert into #tempcmd (phase, cmdtext) values (2, @header)

-- concatenate for Insert case
insert into #tempcmd (phase, cmdtext) values (4, ')')

-- concatenate for Update case
select @header = @wherepc + ' and info.tablenick = @tablenick
'

insert into #tempcmd (phase, cmdtext) values (2, @header)


select @header='
end
else
begin
insert into '
+ @qualname + ' ('
insert into #tempcmd (phase, cmdtext) values (2, @header)

insert into #tempcmd (phase, cmdtext) values (4, '
end'
) -- finishing scope for insert+insert_update

select @header='

if exists (select * from MSmerge_conflicts_info info where tablenick=@tablenick and rowguid='

+ @guid_colname + ' and info.origin_datasource= ' + @original_datasourcecol + ' and info.conflict_type not in (4,7,8,12))
begin
update MSmerge_conflicts_info with (rowlock)
set conflict_type=@conflict_type,
reason_code=@reason_code,
reason_text=@reason_text,
pubid=@pubid,
MSrepl_create_time=@create_time
where tablenick=@tablenick and rowguid='
+ @guid_colname + ' and origin_datasource= ' + @original_datasourcecol + '
and conflict_type not in (4,7,8,12)
end
else
begin

insert MSmerge_conflicts_info with (rowlock)
values(@tablenick, '
+ @guid_colname + ', ' + @original_datasourcecol
+ ', @conflict_type, @reason_code, @reason_text, @pubid, @create_time, @source_id)
end'

insert into #tempcmd (phase, cmdtext) values (5, @header)

if 1 = @maintainsmetadata
begin
exec sys.sp_MScreatedupkeyupdatequery
@tablename= @qualifiedbasetable,
@tablenickstr= @tablenickstr,
@phase= 5,
@isconflictproc= 1,
@tablename2 = @qualifiedbasetable2
end

-- Now we select out the command text pieces in proper order so that our caller,
-- xp_execresultset will execute the command that creates the stored procedure.
select cmdtext from #tempcmd order by phase, step

drop table #tempcmd
drop table #coltab

No comments:

Post a Comment

Total Pageviews