Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmakeconflictinsertproc

  No additional text.


Syntax


-- 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

 
Last revision 2008RTM
See also

  sp_MScreateandsetarticleprocs (Procedure)
sp_MScreatelightweightarticleprocs (Procedure)
sp_MSdrop_rlrecon (Procedure)
sp_MSgetconflictinsertproc (Procedure)
sp_MSscriptsubscriberprocs (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