Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmakeconflicttable

  No additional text.


Syntax
create procedure sys.sp_MSmakeconflicttable (
    @article sysname,
    @publication sysname,
    @creation_mode bit = 0,     -- 0 = for publisher, 1 = for subscriber (snapshot)
    @is_debug bit = 0,
    @alter bit = 0,	-- 1 for alter table case, 0 for create
    @usesqlclr bit = 0
)
as
begin
    
    -- variables
    
    declare @retcode        int

    if @usesqlclr = 1
    begin
        begin transaction
        save transaction sp_MSmakeconflicttable_sqlclr

        exec @retcode = sys.sp_MSmakeconflicttable_sqlclr
            @article = @article,
            @publication = @publication,
            @for_subscriber = @creation_mode,
            @alter = @alter
        if @@error <> 0 or @retcode <> 0 goto CLRFAILURE

        if @creation_mode = 0
        begin
            declare @qualified_conflict_table_name nvarchar(1000)
            declare @conflict_table_schema nvarchar(1000)

            select @conflict_table_schema = schema_name(objectproperty(sau.conflict_tableid, 'SchemaId')),
                   @qualified_conflict_table_name = quotename(schema_name(objectproperty(sau.conflict_tableid, 'SchemaId'))) + N'.' + quotename(object_name(sau.conflict_tableid))
              from dbo.sysarticleupdates sau
            inner join dbo.sysarticles sa
                on sau.pubid = sa.pubid
               and sau.artid = sa.artid
            inner join dbo.syspublications sp
                on sa.pubid = sp.pubid
            where sa.name = @article
               and sp.name = @publication

            if @conflict_table_schema in (N'dbo',N'INFORMATION_SCHEMA')
            begin
                exec @retcode = sys.sp_MS_marksystemobject @qualified_conflict_table_name
                if @@error <> 0 or @retcode <> 0 goto CLRFAILURE
            end
        end

        commit transaction
        return 0
CLRFAILURE:
        rollback transaction sp_MSmakeconflicttable_sqlclr
        commit transaction
        return 1

    end
    else
    begin

    declare @cmd           nvarchar(4000)
            ,@qualname      nvarchar(1000)
            ,@basetablename nvarchar(1000)
            ,@id            int
            ,@colid     int
            ,@conflict_column_id int
            ,@colname   nvarchar(1000)
            ,@conflict_colname   nvarchar(1000)
            ,@col            nvarchar(1000)
            ,@coltype        nvarchar(1000)
            ,@conflict_coltype        nvarchar(1000)
            ,@iscolnullable bit
            ,@conflict_iscolnullable bit
            ,@dbname         nvarchar(1000)
            ,@ownername      nvarchar(1000)
            ,@tablename      nvarchar(1000)
            ,@basetableid   int
            ,@isset         int
            ,@tabid         int
            ,@artid         int
            ,@pubid         int
            ,@indid         int
            ,@indkey        int
            ,@key            nvarchar(1000)
            ,@indexname      nvarchar(1000)
            ,@mode_publisher bit
            ,@mode_subscriber bit
            ,@is_queued     bit
            ,@conflict_isnullable bit
            ,@alter_column bit
            ,@userdefinedtypestobasetypes bit
            ,@xmltontext bit
            ,@maxtypestomatchingnonmaxtypes bit
            ,@schema_option_lodword int

    set nocount on
    select @dbname = db_name()
            ,@mode_publisher = 0
            , @mode_subscriber = 1
            ,@alter_column = 0
            ,@userdefinedtypestobasetypes = 0
            ,@xmltontext = 0
            ,@maxtypestomatchingnonmaxtypes = 0

    
    -- Check and make sure the base table exists
    
    select  @artid = a.artid, @basetableid = a.objid,
            @basetablename = object_name(a.objid),
            @pubid = a.pubid, @is_queued = isnull(p.allow_queued_tran, 0)
            ,@ownername = case @creation_mode when @mode_publisher then schema_name(objectproperty(a.objid, 'SchemaId'))
				else a.dest_owner end
            ,@schema_option_lodword = sys.fn_replgetbinary8lodword(schema_option)
    from sysarticles a, syspublications p
    where   a.name = @article and
            p.name = @publication and
            a.pubid = p.pubid
    if (@basetableid is null or @basetableid = 0)
    begin
        raiserror(15388, 16, 1, @basetablename)
        return (1)
    end

    if @creation_mode = @mode_subscriber
    begin
        if (@schema_option_lodword & 0x00000020) = 0x00000020 -- UserDefinedTypesToBaseTypes
        begin
            set @userdefinedtypestobasetypes = 1
        end

        if (@schema_option_lodword & 0x10000000) = 0x10000000 -- XmlToNText
        begin
            set @xmltontext = 1
        end

        if (@schema_option_lodword & 0x20000000) = 0x20000000 -- MaxTypesToMatchingNonMaxTypes
        begin
            set @maxtypestomatchingnonmaxtypes = 1
        end
    end

    
    -- If the publication does not allowed queued tran, return
    
    if (@is_queued != 1)
        return 0

    
    -- base table should be owner qualified
    
    select @basetablename = QUOTENAME(@ownername) + N'.' + QUOTENAME(@basetablename)

    
    -- Prepare the name for the Conflict table, index
    
    if (@creation_mode = @mode_publisher and @alter = 0)
    begin
        
        -- creating on publisher - get unique names for table, index
        
        exec @retcode = sys.sp_MSgettranconflictname @publication=@publication,
                            @source_object=@basetablename,
                            @str_prefix='conflict_',
                            @conflict_table=@tablename OUTPUT
        if (@retcode != 0 or @@error != 0)
        begin
            raiserror(21542, 16, 1, @@error, 'sp_MSgettranconflictname')
            return (1)
        end

        exec @retcode = sys.sp_MSgettranconflictname @publication=@publication,
                            @source_object=@basetablename,
                            @str_prefix='cftind_',
                            @conflict_table=@indexname OUTPUT
        if (@retcode != 0 or @@error != 0)
        begin
            raiserror(21542, 16, 2, @@error, 'sp_MSgettranconflictname')
            return (1)
        end
    end
	else
    begin
        
        -- creating for subscriber, or doing an alter
        -- get the names from existing table on publisher
        
        select @id = conflict_tableid, @tablename = OBJECT_NAME(conflict_tableid)
        from sysarticleupdates
        where artid = @artid and pubid = @pubid

        exec @indid = sys.sp_MStable_has_unique_index @id
        if (@indid = 0)
        begin
            raiserror(21750, 16, 1, @tablename)
            return (1)
        end

        select @indexname = name
        from sysindexes
        where indid = @indid and id = @id
    end

    
    -- Qualify the Conflict tablename
    
    select @qualname = case
        when (@ownername is null or @ownername = ' ') then QUOTENAME(@tablename)
                else QUOTENAME(@ownername) + '.' + QUOTENAME(@tablename) end

    
    -- begin tran
    
    begin tran
	save tran sp_MSmakeconflicttable

    
    -- create table to select the command text out of
    
    if exists (select * from sys.objects where name = 'tempcmd' and schema_id = schema_id('dbo'))
        drop table dbo.tempcmd

    create table dbo.tempcmd (step int identity NOT NULL, cmdtext nvarchar(4000) NULL)

    
    -- To check if specified object exists in current database drop it if it exists
    -- script the exists and drop code
    
    select @id = object_id(@qualname)
    if (@creation_mode = @mode_publisher and @id is not NULL) or (@creation_mode = @mode_subscriber)
    begin
        select @cmd = N'if object_id(N''' + sys.fn_replreplacesinglequote(@qualname) collate database_default + ''') is not null begin ' +
                    N'DROP TABLE ' + @qualname + N' end
	'
	        insert into dbo.tempcmd(cmdtext) values(@cmd)
    end

    select @cmd = N'CREATE TABLE ' + @qualname + N'('
    									
    insert into dbo.tempcmd(cmdtext) values(@cmd)
    
    -- Declare the cursor to get info on each column of base table
    
    declare #hcurColumnInfo cursor local FAST_FORWARD FOR
        select sc.column_id,
        sc.is_nullable
        from sys.columns sc join sysarticlecolumns ac
			on sc.column_id = ac.colid
        where sc.is_computed = 0
			and sc.object_id = @basetableid
			and ac.artid = @artid
        order by sc.column_id
    FOR READ ONLY

    select @cmd = NULL
    open #hcurColumnInfo
    fetch #hcurColumnInfo into @colid, @iscolnullable
    while (@@FETCH_STATUS = 0)
    begin

        -- Get the typestring for this column
        -- Skip this column if it is NULL
        exec sys.sp_MSget_type @basetableid, @colid, @colname output, @coltype OUTPUT, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes
        if (@@ERROR!= 0)
        begin
            raiserror(21542, 16, 1, @@error, 'sp_MSget_type')
            rollback tran sp_MSmakeconflicttable
			commit tran
			return (1)
       	end

		if (@coltype IS not NULL)
		begin
	        -- Initialize
	        if (@cmd is NULL)
	             	select @cmd = N'    '
	        else
	             select @cmd = N'    ,'
	
	        -- Create the column info
	        select @cmd = @cmd + quotename(@colname) + N' '
	        select @cmd = @cmd + @coltype
	
	        -- Apply nullability
	        if (@iscolnullable = 1)
	             select @cmd = @cmd + N' NULL'
	        else
	             select @cmd = @cmd + N' NOT NULL'
	
	        -- insert into the temptable
	        insert into dbo.tempcmd(cmdtext) values(@cmd)
		end
        -- do the next fetch
FETCHNEXT:
        fetch #hcurColumnInfo into @colid, @iscolnullable
    end

    close #hcurColumnInfo
    deallocate #hcurColumnInfo

    
    -- Now add the conflict related columns, only for create, not for alter
    
	    insert into dbo.tempcmd(cmdtext) values(N'  ,origin_datasource nvarchar(255) NULL
    	,conflict_type int NULL
	    ,reason_code int NULL
    	,reason_text nvarchar(720) NULL
	    ,pubid int NULL
	    ,tranid nvarchar(40) NULL
	    ,insertdate datetime NOT NULL
    	,qcfttabrowid uniqueidentifier DEFAULT NEWID() NOT NULL)
	    ')
    
    -- Create an unique index - we add some more fields to the index of base table
    
    exec @indid = sys.sp_MStable_has_unique_index @basetableid
    if (@indid = 0)
    begin
        raiserror(21750, 16, 1, @basetablename)
        rollback tran sp_MSmakeconflicttable
		commit tran
        return (1)
    end

    insert into dbo.tempcmd(cmdtext) values(N'
    CREATE UNIQUE INDEX ' + quotename(@indexname) + ' ON ' +  @qualname  + N'(')

    select @cmd = NULL
    select @indkey = 1
    while (@indkey <= 16)
    begin
        select @key = index_col(@basetablename, @indid, @indkey)
        if (@key is not null)
        begin
            -- make sure we are replicating this column
            begin
                -- map the index to the right column in base table
                exec sys.sp_MSget_col_position @basetableid, @artid, @key, @col output, @colid output
                exec @isset = sys.sp_isarticlecolbitset @colid, @artid
            end

            if (@isset = 1)
            begin
                if (@cmd is NULL)
                    select @cmd = quotename(@key)
                else
                    select @cmd = @cmd + N', ' + quotename(@key)
            end
        end
        select @indkey = @indkey + 1
    end

    
    -- Add two more fields in the index
  
    if (@cmd is NULL)
        select @cmd = N'tranid, qcfttabrowid'
    else
        select @cmd = @cmd + N', tranid, qcfttabrowid'
    insert into dbo.tempcmd(cmdtext) values(@cmd + N')')

    
    -- If we are creating on publisher
    -- create the table now and update sysarticleupdates now
    
    if (@creation_mode = @mode_publisher)
    begin
        if (@is_debug = 0)
        begin
            
            -- create the table now
            
            select @cmd = 'select cmdtext from dbo.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')
                rollback tran sp_MSmakeconflicttable
				commit tran
                return (1)
            end

            
            -- update sysarticleupdates
            
			if @alter = 0
			begin
				--temp rollback from bug#20002180, remember to change back to object_id()
				--once we decide the owner of conf table here.
	            select @tabid = id from dbo.sysobjects where name = @tablename
    	        if (@tabid = 0 or @tabid is NULL)
        	    begin
            	    raiserror(21286, 16, 1, @tablename)
	                rollback tran sp_MSmakeconflicttable
					commit tran
    	            return (1)
        	    end
	            else
    	        begin
        	        update dbo.sysarticleupdates set conflict_tableid = @tabid
            	        where artid = @artid and pubid = @pubid

	                -- mark the table as system object
    	            if (@ownername in ('dbo','INFORMATION_SCHEMA'))
        	        begin
            	        exec @retcode = dbo.sp_MS_marksystemobject @qualname
	                    if (@@error != 0 or @retcode != 0)
    	                begin
        	                -- roll back the tran
            	            raiserror(21542, 16, 1, @@error, 'sp_MS_marksystemobject')
                	        rollback tran sp_MSmakeconflicttable
							commit tran
	                        return (1)
    	                end
        	        end
	            end
			end
        end
        else
            select cmdtext from dbo.tempcmd order by step
    end

    
    -- commit the tran
    
    commit tran

    
    -- If we are creating for subscriber then
    -- just to a select on the temp table
    
    if (@creation_mode = @mode_subscriber)
    begin
        select cmdtext from dbo.tempcmd order by step
    end

    -- drop the table we created
    drop table dbo.tempcmd
    end -- @usesqlclr = 0
    return 0
end

 
Last revision 2008RTM
See also

  sp_articlesynctranprocs (Procedure)
sp_MSdrop_asm_tranrepl (Procedure)
sp_MSdrop_repltran (Procedure)
sp_scriptsubconflicttable (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