Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_mergearticlecolumn

  No additional text.


Syntax

create procedure sys.sp_mergearticlecolumn (
    @publication sysname,			/* The publication name */
    @article sysname,				/* The article name */
    @column sysname = NULL,			/* The column name */
    @operation nvarchar(4) = 'add',		 /* Add or delete a column */
    @schema_replication nvarchar(5) = 'false',	/* reserved for internal use */
    @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */
    @force_reinit_subscription bit = 0	/* Force reinit subscription */
) AS

    SET NOCOUNT ON

    declare @mergepublish    int
    declare @iscomputed      int
    declare @xtype           int
    declare @sync_mode       int
    declare @index_cnt       int
    declare @indid           int
    declare @in_partition    bit
    declare @colid           int
    DECLARE @cnt int, @idx  int    /* Loop counter, index */
    DECLARE @columnid smallint     /* Columnid-1 = bit to set */
    DECLARE @columns binary(128)         /* Temporary storage for the converted column */
    DECLARE @pubid uniqueidentifier                     /* Publication identification number */
    DECLARE @retcode int                /* Return code for stored procedures */
    DECLARE @artid uniqueidentifier
    declare @object_view   sysname
    declare @filter_clause nvarchar(1000)
    DECLARE @objid int              /* Article base table id */
    declare @publisher    sysname
    declare @publisher_db sysname
    declare @pkkey        sysname
    declare @conflict_table sysname
    declare @status_value   int
    declare @column_list    nvarchar(max)
    declare @ins_conflict_proc sysname
    declare @qual_source_object nvarchar(270)
    declare @qual_object_view   nvarchar(270)
    declare @qual_tmp_object    nvarchar(270)
    declare @source_object    nvarchar(300)
    declare @quoted_source_object nvarchar(270)
    declare @column_name sysname
    declare @compatlevel int
    declare @identity_support int
    declare @automatic_reinitialization_policy bit
    declare @got_merge_admin_applock bit
    declare @column_tracking int
    declare @filestream_col_published int
    declare @has_filestream int

    select @got_merge_admin_applock = 0
    select @filestream_col_published = 0

    select @publisher = publishingservername()
    select @publisher_db = db_name()
    /*
    ** Security Check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    select @mergepublish = 0x4000
    --select @v_unique_index     = 2        -- status in sysindexes
    --select @v_unique_constraint         = 4096 --status in sysindexes

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF ( (SELECT category & 4 FROM master.dbo.sysdatabases WHERE name = DB_NAME() collate database_default) = 0 )
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:     @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication', 'sp_mergearticlecolumn')
            RETURN (1)
        END

    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0 or @@ERROR<>0
            RETURN (1)

    SELECT @pubid = pubid,
           @sync_mode = sync_mode,
           @compatlevel = backward_comp_level,
           @automatic_reinitialization_policy = automatic_reinitialization_policy
        FROM dbo.sysmergepublications WHERE name = @publication
                                        and LOWER(publisher)=LOWER(@publisher)
                                        and publisher_db = @publisher_db

    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
     RETURN (1)
        END

    /*
    ** Parameter Check:     @article.
    ** Check to make sure that the article exists in the publication.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article', 'sp_mergearticlecolumn')
            RETURN (1)
        END

    EXECUTE @retcode = sys.sp_validname @article
    IF @retcode <> 0 or @@ERROR<>0
            RETURN (1)

    /*
    ** Make sure the article exists.
    */
    SELECT @artid = artid, @column_tracking = column_tracking FROM dbo.sysmergearticles
       WHERE pubid = @pubid AND name = @article
    IF @artid IS NULL
        BEGIN
            RAISERROR (20027, 16, -1, @article)
            RETURN (1)
        END

    /*
    ** Parameter Check:     @column.
    ** Check to make sure that the column exists and conforms to the rules
    ** for identifiers.
    */

    IF @column IS NOT NULL
        BEGIN
            EXECUTE @retcode = sys.sp_validname @column
            IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
        END

    /*
    ** Parameter Check:     @operation.
    ** The operation can be either 'add' or 'drop'.
    */
    IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('add', 'drop')
        BEGIN
            RAISERROR (14019, 16, -1)
            RETURN (1)
        END

    /*
    ** column name can not be null for 'drop' operation. OK for 'add' operation
    */
    IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS)='drop' and @column is NULL
        BEGIN
            RAISERROR(14043, 16, -1, '@column', 'sp_mergearticlecolumn')
            RETURN (1)
        END

    declare @internalcallerbit bit
    exec @retcode = sys.sp_MScheckcontext_internalcaller @internalcallerbit OUTPUT
    if @retcode <>0 or @@ERROR<>0
        return 1

    /*
    ** @schema_replication must only be set to FALSE by user code, only internal code can set this to TRUE 
    ** Check the mergearticlecolumn_internalcaller bit to verify that all internal code is using this FLAG.
    */
    IF LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='true' and (@internalcallerbit & 1 = 0)
        BEGIN
            RAISERROR(21490, 16, -1, '@schema_replication', 'FALSE')
            RETURN (1)
        END


    /*
    ** Can not drop non-identity, non-timestamp, non-computed columns that are not nullable and have no default value
    */
    SELECT @status_value=status, @objid = objid,
        @source_object = object_name(objid),@identity_support = identity_support FROM dbo.sysmergearticles WHERE artid = @artid and pubid=@pubid

    select @colid=column_id from sys.columns where object_id=@objid and name=@column
    if not exists (select * from sys.columns where object_id = @objid and name=@column and (is_nullable = 1
                    OR system_type_id=type_id('timestamp') OR is_computed = 1))
        and not exists (select * from sysconstraints where id=@objid and colid=@colid and OBJECTPROPERTY ( constid , 'IsDefaultCnst' ) = 1)
            and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS)='drop' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
            and ColumnProperty(@objid, @column, 'IsIdentity') <> 1
    BEGIN
        RAISERROR(21165, 16, -1, @column)
        return (1)
    END


    /*
    ** Make sure that the column  is not NULL - if NULL set to 0x00.
    */
    SELECT @columns = columns FROM dbo.sysmergearticles WHERE artid = @artid and pubid=@pubid
    IF @columns IS NULL
    begin
        select @columns = 0x00
        UPDATE dbo.sysmergearticles SET columns = 0x00 WHERE artid = @artid and pubid=@pubid
    end


    /*
    **    Make sure that you are not trying to add more than 246 columns to the table.
    */

    IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add'
    BEGIN
        IF @column IS NULL
        BEGIN
            select @cnt = count(*) from sys.columns
                where object_id=@objid and
                     is_computed<>1 and
                     system_type_id<>type_id('timestamp')

            IF @cnt > 246
            BEGIN
                RAISERROR(25006, 16, -1, @article, 246)
                RETURN (1)
            END

        END
        ELSE
        BEGIN
            exec sp_MSBitmapCount @bm1=@columns, @count = @cnt output
            IF @cnt >= 246
            BEGIN
                RAISERROR(25006, 16, -1, @article, 245)
                RETURN (1)
            END
        END
    END

    -- Colvs can only handle 246 columns so we can not allow more than 246 columns to exist on a table
    -- if column tracking is being used. The reason is that we track all the columns even when vertical
    -- partitioning is used and only some of the columns are published.
    if @column_tracking = 1
    begin
        select @cnt = count(*) from sys.columns
            where object_id=@objid
        IF @cnt > 246
        BEGIN
            RAISERROR(25020, 16, -1, @article, 246)
            RETURN (1)
        END
    end


    if LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
    begin
        select @indid = index_id from sys.indexes where object_id = @objid and is_primary_key <> 0    /* PK index */
        select @index_cnt = 1
        while (@index_cnt <= 16)
        begin
            select @pkkey = INDEX_COL(@source_object, @indid, @index_cnt)
            if @pkkey is NULL
                break
            if @pkkey=@column
            begin
                raiserror(21250, 16, -1, @column)
                return (1)
            end
            select @index_cnt = @index_cnt + 1
        end


        /* Not allowed to drop identity column if identity_support is set to 1 */

        if @identity_support = 1
        begin
            if exists (    select * from sys.columns where object_id = @objid  and
                                                        @column = name        and
                                                        is_identity = 1)
            begin
                raiserror(22550, 16, -1, @column)
                return (1)
            end

        end


        /*
        ** Check for unique index defined on this column - to disallow such a column from being dropped
        */
        if exists (select * from sys.indexes where object_id=@objid
                    --and (status & @v_unique_index = @v_unique_index
                    --    or status & @v_unique_constraint = @v_unique_constraint))
                    and (is_unique = 1 or is_unique_constraint = 1))
        begin
            declare @keys varbinary(816)
            declare @i        int
            declare #check_unique CURSOR LOCAL FAST_FORWARD for
            select index_id from sys.indexes where object_id=@objid
                    and (is_unique = 1 or is_unique_constraint = 1)
            open #check_unique
            fetch #check_unique into @indid
            while (@@fetch_status<>-1)
            begin
                SELECT @i = 1
                WHILE (@i <= 16)
                BEGIN
                        SELECT @pkkey = INDEX_COL(@source_object, @indid, @i)
                        if @pkkey is NULL
                            break
                        if @pkkey=@column
                        BEGIN
                            if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='true'
                                raiserror(21265, 16, -1, @column, @source_object)
                            else
                                raiserror(21347, 16, -1, @column)
                            close #check_unique
                   deallocate #check_unique
                            return (1)
                        END
                        select @i = @i + 1
                END
                fetch #check_unique into @indid
            end
            close #check_unique
            deallocate #check_unique
        end
    end

    begin tran
    save TRANSACTION articlecolumn

    exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
                                                  @lockowner = N'Transaction'
    if @retcode<>0 or @@error<>0
    begin
        raiserror(20713, 16, -1, 'sp_mergearticlecolumn', @publication)
        goto FAILURE
    end

    select @got_merge_admin_applock = 1

    -- Get exclusive object lock upfront so subsequent %%ColumnEx can proceed
    exec sys.sp_MSget_qualified_name @objid, @qual_source_object OUTPUT
    if @qual_source_object is null
        goto FAILURE

    exec %%Object(MultiName = @qual_source_object).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
    --exec %%Object(MultiName = @qual_source_object).LockExclusiveMatchID(ID = @objid)
    if @@error <> 0
        goto FAILURE

    /*
    ** If no columns are specified, or if NULL is specified, set all
    ** the bits in the 'columns' column so all columns will be included,
    */
    IF @column IS NULL
    BEGIN
        SELECT @cnt = max(column_id), @idx = 1 FROM sys.columns WHERE object_id = @objid
        SELECT @columns = NULL
        WHILE @idx <= @cnt
        BEGIN
            /* to make sure column holes will not be included */
            if exists (select * from sys.columns where column_id=@idx and object_id=@objid and
                (@sync_mode=0 OR (is_computed<>1 and system_type_id <> type_id('timestamp'))))
            begin
                exec sys.sp_MSsetbit @bm=@columns OUTPUT, @coltoadd=@idx, @toset = 1
                if @@ERROR<>0 or @retcode<>0
                    goto FAILURE

                select @column_name = name, @has_filestream = is_filestream  from sys.columns where object_id=@objid and column_id=@idx
                if @@error<>0
                    goto FAILURE
                exec %%ColumnEx(ObjectID=@objid, Name=@column_name).SetMergePublished(Value=1)
                if @@ERROR<>0
                    goto FAILURE

                if ( @filestream_col_published <> 1  and @has_filestream = 1 )
                    select @filestream_col_published = 1

            end
            SELECT @idx = @idx + 1
        END
        UPDATE dbo.sysmergearticles SET columns = @columns WHERE name = @article AND pubid = @pubid
        if @sync_mode=1 and exists (select * from sys.columns where object_id=@objid and (is_computed=1 or system_type_id=type_id('timestamp')))
            UPDATE dbo.sysmergearticles SET vertical_partition = 1 WHERE name = @article AND pubid = @pubid
    END
    ELSE
    BEGIN
        /* if @column is NULL, meanning all columns are in, do not bump up version to Shiloh. */
        if @compatlevel < 40
        begin
            if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
                raiserror(21351, 10, -1, @publication)
            else
                raiserror(21352, 10, -1, @publication)
            exec @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
            if @@ERROR<>0 or @retcode<>0
                GOTO FAILURE
        end

        SELECT @columnid = column_id, @iscomputed=is_computed, @xtype=system_type_id, @has_filestream = is_filestream
            FROM sys.columns WHERE object_id = @objid AND name = @column
        IF ((@@error <> 0) OR (@columnid IS NULL))
        BEGIN
            RAISERROR (21166, 16, -1, @column)
            GOTO FAILURE
        END

        if ( @filestream_col_published <> 1  and @has_filestream = 1 )
            select @filestream_col_published = 1

        /*
 ** for character mode publications, we do not allow adding computed column or timestamp columns
        ** into the vertical parititioning.
        */
        if @sync_mode=1 and (@iscomputed = 1 or type_name(@xtype) ='timestamp') and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add'
        begin
            if LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
                begin
                    raiserror(21269, 16, -1)
                    GOTO FAILURE
                end
            else
            begin
                if @@TRANCOUNT >0
                begin
                    ROLLBACK TRANSACTION articlecolumn
                    COMMIT TRAN
                end
                return (0)
            end
        end

        if ColumnProperty(@objid, @column, 'isrowguidcol') = 1 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
        begin
            RAISERROR(21162, 16, -1)
            GOTO FAILURE
        end

        -- for computed columns, make sure all dependent cols are published if add
        if @iscomputed = 1
        begin
            declare @refid int, @isinpartition smallint
            declare @ref_major_id int
            declare @ref_object_name nvarchar(512)
            select top 1 @refid=referenced_minor_id, @ref_major_id=referenced_major_id from sys.sql_dependencies where column_id=@columnid and class =1 and object_id=@objid order by referenced_minor_id
            while @refid is not null
            begin

				if @objid = @ref_major_id	-- columns within the same table.
				begin
	                -- check its dependent cols are published
	                exec @isinpartition = sys.sp_MStestbit @bm=@columns, @coltotest=@refid
	                if @isinpartition=0
	                begin
	                    RAISERROR(21549, 16, -1, @column)
	                    GOTO FAILURE
	                end
				end
				else	-- other objects that this depends on.
				begin
	                -- check that the objects it depends on are published only if the referenced object is a table,view,proc or function.
	                if 	exists (select * from sys.objects where object_id = @ref_major_id and type in ('U','V','P','FN')) and
	                	not exists (select * from dbo.sysmergearticles where objid = @ref_major_id and pubid = @pubid) and
	                	not exists (select * from dbo.sysmergeschemaarticles where objid = @ref_major_id and pubid = @pubid)
	                begin
	                	exec sys.sp_MSget_qualified_name @ref_major_id, @ref_object_name OUTPUT
	                    RAISERROR(20723, 16, -1, @column, @ref_object_name)
	                    GOTO FAILURE
	                end
				end

                -- next loop
                select top 1 @refid=referenced_minor_id, @ref_major_id=referenced_major_id from sys.sql_dependencies where column_id=@columnid and class =1 and object_id=@objid and referenced_minor_id>@refid order by referenced_minor_id
                if @@rowcount = 0
                    set @refid = NULL
            end
        end

        -- if drop, make sure the computed columns that depends on it is already dropped
        if LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
        begin
            declare @refid2 int, @isinpartition2 smallint
            select top 1 @refid2=column_id from sys.sql_dependencies where referenced_minor_id=@columnid and class =1 and object_id=@objid order by referenced_minor_id
            while @refid2 is not null
            begin
                -- check its dependent cols are published
                exec @isinpartition2 = sys.sp_MStestbit @bm=@columns, @coltotest=@refid2
                if @isinpartition2<>0
                begin
                    RAISERROR(21550, 16, -1, @column)
                    GOTO FAILURE
                end
                -- next loop
                select top 1 @refid2=column_id from sys.sql_dependencies where referenced_minor_id=@columnid and class =1 and object_id=@objid and column_id>@refid2 order by referenced_minor_id
                if @@rowcount = 0
                    set @refid2 = NULL
            end
        end

        exec @in_partition = sys.sp_MStestbit @bm=@columns, @coltotest=@columnid

        if @in_partition=1 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
        begin
            RAISERROR(21335, 10, -1, @column)
            --GOTO FAILURE    -- Juse because column has already been added to vertical partition doesn't mean we need to error out.
        end

        if @in_partition=0 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop' and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
        begin
            RAISERROR(21336, 10, -1, @column)
            GOTO FAILURE
        end

        SELECT @columns = columns, @filter_clause=subset_filterclause, @ins_conflict_proc=ins_conflict_proc, @conflict_table=conflict_table FROM dbo.sysmergearticles WHERE name = @article AND pubid = @pubid
        IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'add'
            begin
                exec @retcode = sys.sp_MSsetbit @bm = @columns OUTPUT, @coltoadd=@columnid, @toset=1
                if @@ERROR<>0 or @retcode<>0
                    GOTO FAILURE
                select @column_name = name from sys.columns where object_id=@objid and column_id=@colid
                if @@error<>0
                    goto FAILURE
                exec %%ColumnEx(ObjectID=@objid, Name=@column_name).SetMergePublished(Value=1)
                if @@ERROR<>0
                    goto FAILURE
            end
        ELSE
            begin

                exec @retcode = sys.sp_MSsetbit @bm = @columns OUTPUT, @coltoadd=@columnid, @toset=0
                if @@ERROR<>0 or @retcode<>0
                    GOTO FAILURE
                if @columns = 0x00
                    begin
                        raiserror(21345, 16, -1)
                        goto FAILURE
                    end
                exec @retcode = sys.sp_MSclearcolumnbit @pubid, @artid, @column
                if @@ERROR<>0 or @retcode<>0
                    goto FAILURE
            end

        /*
        ** Set vertical_partitioning flag so that publication view would be re-generated even
        ** if there is not subsetfilters nor join filters
        */
        UPDATE dbo.sysmergearticles     SET columns = @columns, vertical_partition=1
            WHERE name = @article AND pubid = @pubid
        IF @@ERROR <> 0
        BEGIN
            RAISERROR (14021, 16, -1)
            GOTO FAILURE
        END

        select @column_list = NULL

        /*
        ** check to see if that column can be dropped based on current article's filter clause
        ** and if the article is involved in any join_filter_clauses - to make sure the drop of
        ** one column does not affect any such joins
        */
        if ((@filter_clause is not NULL and @filter_clause <>'' ) or
            exists (select * from dbo.sysmergesubsetfilters where pubid=@pubid and
            (artid=@artid or join_articlename=@article))) and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
        begin
            exec @retcode = sys.sp_MSgetcolumnlist @pubid, @column_list OUTPUT, @objid
            if @@ERROR<>0 or @retcode<>0
                GOTO FAILURE

            select @object_view='TEMP_VIEW_' + @source_object  --@source_object is not quoted
            select @qual_object_view=quotename(@object_view)

            exec @retcode = sys.sp_MSget_qualified_name @objid, @qual_source_object OUTPUT
            if @@ERROR<>0 or @retcode<>0
        goto FAILURE
            select @quoted_source_object=QUOTENAME(@source_object)

            exec ('create view dbo.' + @qual_object_view + ' as select ' + @column_list + ' from ' + @qual_source_object + ' ' + @quoted_source_object)
            if @@ERROR<>0
                GOTO FAILURE
            if @filter_clause is not NULL and @filter_clause <>''
            begin
                exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @quoted_source_object + ' where ' + @filter_clause)
                if @@ERROR<>0
                    begin
                        exec('drop view ' + @qual_object_view)
                        raiserror(21256, 16, -1, @filter_clause, @source_object)
                        GOTO FAILURE
                    end
            end
        end

        /*
        ** Check to make sure dropping a column will not breaking any other articles that using current article as join_article
        */
        if exists (select * from dbo.sysmergesubsetfilters where pubid=@pubid and
            (artid=@artid or join_articlename=@article)) and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
        begin
            declare @join_articlename sysname
            declare @tmp_artid uniqueidentifier
            declare @join_objid int
            declare @join_object sysname
            declare @qual_join_object nvarchar(517)
            declare @quoted_join_object nvarchar(258)

            declare per_article CURSOR LOCAL FAST_FORWARD FOR
                select artid, join_filterclause, join_articlename from sysmergesubsetfilters
                    where pubid=@pubid and (join_articlename=@article or artid=@artid)
            for READ ONLY
            open per_article
            fetch per_article into @tmp_artid, @filter_clause, @join_articlename
            while (@@fetch_status<>-1)
            begin
                if @artid<>@tmp_artid
                    select @join_objid=objid from sysmergearticles where pubid=@pubid and artid=@tmp_artid
                else
                    select @join_objid=objid from sysmergearticles where pubid=@pubid and name=@join_articlename

                select @join_object = object_name(@join_objid)
                select @qual_join_object = quotename(schema_name(schema_id)) + '.' + quotename(name) from sys.objects where object_id = @join_objid
                select @quoted_join_object = quotename(object_name(@join_objid))

                if @join_object is not NULl and @join_object<>''
                begin
                    exec ('declare @test int select @test=1 from ' + @qual_object_view + ' ' + @quoted_source_object + ', ' + @qual_join_object + ' ' + @quoted_join_object + ' where ' + @filter_clause)
                    if @@ERROR<>0
                    begin
                        close per_article
                        deallocate per_article
                        raiserror(21256, 16, -1, @filter_clause, @source_object)
                        GOTO FAILURE
                    end
                end
                fetch per_article into @tmp_artid, @filter_clause, @join_articlename
            end
            close per_article
            deallocate per_article
        end
    END


   -- filestream_change
    if (@filestream_col_published = 1)
    begin
        -- filestream column is not supported for character mode publications
        if ( @sync_mode = 1 )
        begin
            raiserror(22581, 16, -1, @article, @publication)
            GOTO FAILURE
        end
        -- filestream column is only supported for Yukon+ subscribers as
        -- conversion of filestream column (max type) to base type is not allowed.
        else if ( @compatlevel < 90 )
        begin
            raiserror(22582, 16, -1, @article, @publication)
    GOTO FAILURE
        end
    end

    /*
    ** if snapshot is ready, change it to obsolete to force another snapshot run.
    ** Note this is the third value of snapshot_ready. 0 for not ready, 1 for OK, 2 for obsolete
    */
    IF EXISTS (SELECT * FROM dbo.sysmergepublications WHERE pubid=@pubid and snapshot_ready>0)
            and LOWER(@schema_replication collate SQL_Latin1_General_CP1_CS_AS)='false'
    BEGIN
        update dbo.sysmergearticles set status=1, conflict_table=NULL where pubid=@pubid and artid=@artid and status=2
        if @@ERROR<>0
            goto FAILURE

        update dbo.sysmergearticles set status=5, conflict_table=NULL where pubid=@pubid and artid=@artid and status=6
        if @@ERROR<>0
            goto FAILURE

        /*
        ** Force a re-generation of conflict table and its ins_proc
        */
        if object_id(@ins_conflict_proc) is not NULL
        begin
            set @quoted_source_object= quotename(@ins_conflict_proc)
            exec ('drop proc ' + @quoted_source_object)
            if @@ERROR<>0
                goto FAILURE
        end
        if object_id(@conflict_table) is not NULL
        begin
            set @quoted_source_object= quotename(@conflict_table)
            exec ('drop table ' + @quoted_source_object)
            if @@ERROR<>0
                goto FAILURE
        end


        /*
        ** make sure we know we really want to do this.
        */
        if exists (select * from dbo.sysmergepublications where pubid=@pubid and snapshot_ready<>2)
        begin
            if @force_invalidate_snapshot = 0
                begin
                    raiserror(20607, 16, -1)
                    goto FAILURE
                end
        end
        update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid
        if @@ERROR<>0
            goto FAILURE

        if @force_reinit_subscription = 0 and @status_value<>5 --5 is the value for new_inactive
        begin
            raiserror(20608, 16, -1)
            goto FAILURE
        end

        --do a global re

        if @force_reinit_subscription = 1
        begin
                --global reinitialization will bump up backward-comp-level to SP2.
            exec @retcode = sys.sp_MSreinitmergepublication
                                    @publication = @publication,
                                    @upload_first = @automatic_reinitialization_policy
            if @retcode<>0 or @@ERROR<>0
                goto FAILURE
        end
        /*
        ** Even for vertical partitioning on new article - we do not need to bumpup backward-comp-level
        **
        else
            begin    --bump up the backward-comp-level so that only 80 subscribers can use it.
            exec @retcode = sys.sp_MSBumpupCompLevel @pubid, 40
            if @@ERROR<>0 or @retcode<>0
                GOTO FAILURE
            end
        */
    END

    exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
    COMMIT TRANSACTION
    if object_id(@qual_object_view) is not NULL
    begin
        exec ('drop view ' + @qual_object_view)
    end
    return (0)
FAILURE:

    if @@TRANCOUNT >0
        begin
            if @got_merge_admin_applock=1
                exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
            ROLLBACK TRANSACTION articlecolumn
            COMMIT TRAN
        end

    if object_id(@qual_object_view) is not NULL
    begin
        exec ('drop view ' + @qual_object_view)
    end

    return (1)

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
sp_MSmerge_altertable (Procedure)
sp_repladdcolumn (Procedure)
sp_repldropcolumn (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