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