Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_articlecolumn

  No additional text.


Syntax
create procedure sys.sp_MSrepl_articlecolumn
(
	@publication				sysname,
	@article					sysname,
	@column						sysname = NULL,
	@operation					nvarchar(5) = N'add',
	-- synctran
	@refresh_synctran_procs		int = 1,  --note this param is bit in sp_articlecolumn
										--make it int here so we can overload it to deal with timestamp col
										--0 not to refresh sync proc
										--1 general purpose refresh
										--2 force refresh (only used by DDL trigger)
	@ignore_distributor			bit = 0,
	-- DDL
	@change_active				int = 0, -- 0: non-ddl, 2: ddl
	@force_invalidate_snapshot	bit = 0,
	@force_reinit_subscription	bit = 0,
	-- Peer-To-Peer
	@internal 					bit = 0
)
AS
BEGIN
    /*
    ** Declarations.
    */

    DECLARE @bit tinyint                /* Bit offset */
			,@cnt tinyint, @idx tinyint  /* Loop counter, index */
			,@pubid int                  /* Publication identification number */
			,@retcode int                /* Return code for stored procedures */
			,@artid int
			,@active tinyint
			,@objid int            /* Article base table id */
			,@tablename  sysname
			,@fSynctranColChanged bit
			,@pkkey sysname
			,@indid int
			,@index_cnt int
			,@allow_initialize_from_backup bit
			,@autogen_sync_procs_id bit
			,@sync_pubid int
			,@allow_queued_tran bit
			,@allow_sync_tran bit
			,@options int
			,@OPT_ENABLED_FOR_P2P int
			,@status int
			,@view_name nvarchar(386)
			,@filter_clause nvarchar(4000)
			,@sync_objid int
			,@art_type tinyint
			,@schema_option bigint
			,@is_udt bit
			,@is_xml bit
			,@is_max bit
            ,@is_filestream bit
            ,@sync_method tinyint

    select @active = 2
            ,@OPT_ENABLED_FOR_P2P = 0x1
            ,@fSynctranColChanged = 0
            ,@allow_initialize_from_backup = 0
            ,@operation = lower(@operation)

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

    /*
    ** Check to see if the database has been activated for publication.
    ** Do not check if @ignore_distributor indicates brute force cleanup.
    */
    IF ( (SELECT category & 1
          FROM master.dbo.sysdatabases
         WHERE name = DB_NAME() collate database_default) = 0 )  and ( @ignore_distributor = 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, N'@publication', 'sp_MSrepl_articlecolumn')
        RETURN (1)
    END
    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0
            RETURN (1)
    
    -- get publication metadata
    
    SELECT @pubid = pubid,
        @allow_sync_tran = allow_sync_tran,
        @allow_queued_tran = allow_queued_tran,
        @allow_initialize_from_backup = allow_initialize_from_backup,
        @autogen_sync_procs_id = autogen_sync_procs,
        @sync_pubid = pubid,
        @sync_method = sync_method,
        @options = options
        FROM syspublications WHERE name = @publication
    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, N'@article', 'sp_MSrepl_articlecolumn')
        RETURN (1)
    END
    /*
    	Enforce PeerToPeer restrictions
	    If it is P2P and not from an internal call then it must be called
    	by a user attempting to filter.
    */
    IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
        AND @internal = 0
    BEGIN
        -- Peer-To-Peer publications do not support filtering. Please change the '@publication' parameter value.
        RAISERROR(20646, 16, -1, 'filtering', '@publication')
        RETURN (1)
    END

    /*
    ** Make sure the article exists.
    */
        select @artid = sa.artid
					,@status = sa.status
					,@sync_objid = sa.sync_objid
					,@filter_clause = sa.filter_clause
					,@art_type = sa.type
					,@schema_option = convert(bigint, sa.schema_option)
					,@objid = objid
					,@tablename = OBJECT_NAME(objid)
         FROM sysarticles sa JOIN syspublications sp ON sa.pubid = sp.pubid
         WHERE sa.name = @article
         AND sp.name = @publication

    IF @artid IS NULL
    BEGIN
        RAISERROR (20027, 11, -1, @article)
        RETURN (1)
    END
    /*
    ** Error out if this is a not a table based article
    */
    IF ((@art_type & 1) = 0 )
    BEGIN
        RAISERROR (14112, 11, -1 )
        RETURN (1)
    END
    /*
    ** Parameter Check:  @operation.
    ** The operation can be either 'add' or 'drop'.
    */
    IF (@operation NOT IN (N'add', N'drop', N'alter'))
    BEGIN
        RAISERROR (14019, 16, -1)
        RETURN (1)
    END

	if exists (select * from sys.columns where object_id = @objid and is_column_set = 1)
	begin
		-- ErrorFormat: Can not publish article '%s' or add add ColumnSet column to its base table '%s' because Replication does not support ColumnSet.
        RAISERROR (21864, 16, -1, @article, @tablename)
        RETURN (1)
	end

    if @column is not null
    begin
        declare @colid  int
        select @colid=column_id from sys.columns where object_id=@objid and name=@column
        if @colid is null
        begin
            RAISERROR (14020, 16, -1)
            RETURN (1)
        end

        if (@operation = 'drop')
        begin
            -- Vertical partition is only allowed on table-based article, not IV->table
            IF OBJECTPROPERTY(@objid, 'IsTable') <> 1
            BEGIN
                RAISERROR (14112, 11, -1 )
                RETURN (1)
            END
            -- PK column has to be included in vertical partition
            select @indid = indid
                    ,@index_cnt = 1
            from sysindexes
            where id = @objid and (status & 2048) <> 0    /* PK index */
            while (@index_cnt <= 16)
            begin
                select @pkkey = INDEX_COL(@tablename, @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
            
            -- for updating subscribers
            
            if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
            begin
                
                -- we cannot drop the versioning column from the partition.
                
                if  N'msrepl_tran_version' = @column
                begin
                    RAISERROR (21080, 16, -1)
                    RETURN (1)
                end
                
                -- Only columns that have default values can be outside the partition
                -- Note: do check error if it is schema change.
                
                if @change_active = 0 and ColumnProperty(@objid, @column, N'IsIdentity') <> 1 and
                -- 189 is timestamp.
                    not exists (select * from sys.columns where object_id = @objid and
                                name=@column and (is_nullable=1 or system_type_id = 189)) and
                    not exists (select * from sysconstraints where id=@objid and
                                colid=@colid and OBJECTPROPERTY ( constid , 'IsDefaultCnst' ) = 1)
                BEGIN
                    RAISERROR(21165, 16, -1, @column)
       return (1)
                END
            end -- if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
        end --if (@operation = 'drop')
    end --if @column is not null
    
    -- @ignore_distributor is set to 1 when removing replication forcefully. In that
    -- case, no need to check or reinit
    
    if @ignore_distributor = 0
    begin
        -- Check if there are snapshot or subscriptions and raiserror if needed.
        EXECUTE @retcode  = sys.sp_MSreinit_article
            @publication = @publication,
            @article = @article,
            @need_new_snapshot = 1,
            @need_reinit_subscription = 1
            ,@force_invalidate_snapshot = @force_invalidate_snapshot    /* Force invalidate existing snapshot */
            ,@force_reinit_subscription = @force_reinit_subscription    /* Force reinit subscription */
            ,@check_only = 1
        IF @@ERROR <> 0 OR @retcode <> 0
            return (1)
    end

    begin tran
    save TRANSACTION articlecolumn

    /*
    ** If no columns are specified, or if NULL is specified, set all
    ** the bits in the 'columns' column so all columns will be included.
    */

    DECLARE @columnid smallint   /* Columnid-1 = bit to set */
	if @column IS NULL and @change_active = 2
	begin
       DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
            SELECT colid FROM #tran_columnstable
	end
    else IF @column IS NULL
    BEGIN
       DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
            SELECT column_id FROM sys.columns where
                object_id = @objid
    END
    ELSE
    BEGIN
       DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
            SELECT column_id
    	    FROM sys.columns
        	WHERE object_id = @objid AND name = @column
    END

    OPEN hCartcolumn
    FETCH hCartcolumn INTO @columnid
    if(@@fetch_status = 0 and @change_active = 2) -- post article column partition info for schema replication
    begin
        exec sys.sp_replpostcmd 0, @objid, @artid, 51, @objid
    end

    WHILE (@@fetch_status <> -1)
    BEGIN

        /*
        ** Get the column id for this column.  We'll use the column id
        ** to determine the bit in the 'columns' column.  The bit we want
        ** is equal to the columnid - 1.
        */
        SELECT @column = col.column_id
        			,@is_udt = 	case typ.system_type_id when 240 then 1 else 0 end
					,@is_xml =	case typ.name when N'xml' then 1 else 0 end
					,@is_max = case when typ.name in (N'varchar', N'nvarchar', N'varbinary') and  col.max_length = -1 then 1 else 0 end
                    ,@is_filestream = col.is_filestream
        FROM sys.columns col
		        join sys.types typ on typ.user_type_id = col.system_type_id or (typ.system_type_id = 240 and typ.user_type_id = col.user_type_id)
        WHERE col.object_id = @objid AND col.column_id = @columnid

						
        IF ((@@error <> 0) OR (@columnid IS NULL))
        BEGIN
            RAISERROR (14020, 16, -1)
            goto UNDO
        END

        IF (@is_filestream = 1 AND @sync_method in (5,6) and @operation in (N'add', N'alter'))
        BEGIN
            RAISERROR (21862, 16, -1)
            goto UNDO
        END

        
        -- add/drop the column to partition
        
        if (@status & 32) > 0 and
            exists (select * from sys.columns WHERE object_id = @objid and system_type_id = 189 and column_id = @columnid)
        begin
            
            -- adding timestamp column as timestamp on subscribers
            -- do not include in partition when adding and set explicit column name list
            -- just unmark status when dropping
            
            update sysarticles set
                status = case
                    when (@operation = N'add') then (status | 8)  -- add
                    when (@operation = N'drop') then (status & ~32)  -- drop
              else status end
            where artid = @artid
            if @@error <> 0
                goto UNDO

            
            -- Always try to remove any timestamp columns if status & 32 != 0
            
            delete dbo.sysarticlecolumns
             where artid = @artid
               and colid = @columnid
            if @@error <> 0
                goto UNDO

            
            -- mark for synctran proc refresh
            
            select @fSynctranColChanged = 1
        end
        else
        begin -- not replicating timestamp as timestamp
            IF @operation = N'add'
            begin
                if not exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
                begin
                    insert dbo.sysarticlecolumns (artid, colid, is_udt, is_xml, is_max) values (@artid, @columnid, @is_udt, @is_xml, @is_max)
                    select @fSynctranColChanged = 1
                end
            end
            else IF @operation = N'drop'
            begin
                if exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
                begin
						
					/* Update column published status  - we must call this before deleting from dbo.sysarticlecolumns */
                    /* see bug 335014 */
					EXECUTE @retcode = sys.sp_MSarticlecol @artid, @columnid,
											N'publish', @operation
					IF (@@error <> 0 OR @retcode <> 0)
					BEGIN
						RAISERROR (14021, 16, -1)
						goto UNDO
					END	
                    delete from dbo.sysarticlecolumns where artid = @artid and colid = @columnid
                    select @fSynctranColChanged = 1

                end
            end
            else IF @operation = N'alter'
            begin
                if exists (select artid, colid from dbo.sysarticlecolumns where artid = @artid and colid = @columnid)
                begin
                    select @fSynctranColChanged = 1
                end
            end

            IF @@error <> 0
            BEGIN
                RAISERROR (14021, 16, -1)
                goto UNDO
            END
            /*
            ** if the status has changed, call sp_MSarticlecol to update the publication
            ** status as appropriate.
            */
            if(@fSynctranColChanged = 1 and @operation <> N'drop')
            begin
                /* Update column published status */
                EXECUTE @retcode = sys.sp_MSarticlecol @artid, @columnid,
                                        N'publish', @operation
                IF (@@error <> 0 OR @retcode <> 0)
                BEGIN
                    RAISERROR (14021, 16, -1)
                    goto UNDO
                END
            end
        end -- not replicating timestamp as timestamp

        -- fetch the next column
        FETCH hCartcolumn INTO @columnid

    END -- end of while block

	--once we are here, article partition has been updated up to current point, now check on UDT dependency
	--exclude DDL case as we need to check in DDL trigger for adding check constraint anyway
	if (@change_active != 2) and
		(@schema_option & 0x000000B000000020 <> 0) and
		exists (select * from sys.columns where object_id = @objid and system_type_id = 240)
	begin
		if(sys.fn_MSrepl_dependUDT(@objid, @schema_option, @artid) = 1)
		begin
			raiserror (21839, 16, 1, @article)
			goto UNDO
		end
	end

    
    -- active article fixups
    

    if @change_active<> 0 and @fSynctranColChanged = 1 or
        -- Besides schema change, we automatically refresh article view if there are
        -- subscriptions. We don't refresh the article view otherwise to avoid the view
        -- being dropped and recreated when adding columns into the partition during
        -- the creation of the article.
        exists (select * from syssubscriptions where artid = @artid and
            srvid >= 0)
    BEGIN
        
        -- regenerate the article view
        
        -- Only invoke sp_articleview if not manual view and not manual filter
        if ( @art_type & 0x4 <> 4 and @art_type & 0x2 <> 2 )
        begin
            select @view_name = object_name( @sync_objid )

            exec @retcode = sys.sp_articleview @publication = @publication,
                                            @article = @article,
                                            @view_name = @view_name,
                                            @filter_clause = @filter_clause,
                                            @change_active = @change_active,
                                            @force_invalidate_snapshot = @force_invalidate_snapshot,
                                            @force_reinit_subscription = @force_reinit_subscription,
                                            @refreshsynctranprocs = 0,
                                            @internal = @internal
            IF @@ERROR <> 0 OR @retcode <> 0
                    goto UNDO
        end
    END
    
    -- If publication is enabled for Synctran and sprocs are auto-generated.
    -- Do the synctran proc generation after article view is regenerated
    
    if  @autogen_sync_procs_id = 1 and
    (@refresh_synctran_procs = 1 and @fSynctranColChanged = 1 or
    @refresh_synctran_procs = 2  -- this is to special case for drop timestamp col in queued article, since @fSynctranColChanged won't be set
    )
    begin
        exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 0
-- UNDO change to 1 when nvarchar(max) support on xml data is in
--        exec @retcode = sys.sp_articlesynctranprocs @publication, @article, @autogen_sync_procs_id, 1
        IF @@ERROR <> 0 OR @retcode <> 0
            goto UNDO
    end

    -- schema replication used @change_active = 2 to prepare, don't invalidate or reinitialize
    if @change_active <> 2  and
        -- @ignore_distributor is set to 1 when removing replication forcefully. In that
        -- case, no need to check or reinit
        @ignore_distributor = 0
    begin
        -- Have to call this stored procedure to invalidate existing snapshot or reint
        -- subscriptions if needed
        EXECUTE @retcode  = sys.sp_MSreinit_article
            @publication = @publication,
            @article = @article,
            @need_new_snapshot = 1,
            @need_reinit_subscription = 1
            ,@force_invalidate_snapshot = @force_invalidate_snapshot    /* Force invalidate existing snapshot */
            ,@force_reinit_subscription = @force_reinit_subscription    /* Force reinit subscription */
        IF @@ERROR <> 0 OR @retcode <> 0
            GOTO UNDO
    end

    /*
    ** Force the article cache to be refreshed with the new definition.
    ** Nothing to flush if brute force cleanup.
    */
    if ( @ignore_distributor = 0 )
        EXECUTE sys.sp_replflush


    if ( @allow_initialize_from_backup = 1)
    begin
        exec @retcode = sys.sp_MSincrementpublicationminautonosynclsn @publication = @publication
        if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto UNDO end
    end


    COMMIT TRANSACTION
END
return (0)
UNDO:
    if @@trancount > 0
    begin
        ROLLBACK TRANSACTION articlecolumn
        commit tran
    end
    RETURN (1)

 
Last revision 2008RTM
See also

  sp_articlecolumn (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MStran_altertable (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