Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSarticlecol

  No additional text.


Syntax
create procedure sys.sp_MSarticlecol
(
    @artid int,
    @colid int = NULL,
    @type nvarchar(10),      -- 'publish', 'nonsqlsub'
    @operation nvarchar(5)  -- 'add', 'drop'
)
AS
begin
    set nocount on
    -- Declarations.
    DECLARE @tabid int        --Article base table id
                ,@retcode int
                ,@status bit
                ,@allow_dts bit
                , @pubid int  -- hydra compatible colid
                ,@colname sysname
                ,@objname nvarchar(517)
    declare @coltable table(name sysname, column_id int, is_computed bit)

    SELECT @tabid = sa.objid, @pubid = sa.pubid, @allow_dts = sp.allow_dts
    FROM sysarticles as sa join syspublications as sp
            on sa.pubid = sp.pubid
    WHERE sa.artid = @artid

    -- Get qualified object name for error logging
    EXEC sys.sp_MSget_qualified_name @tabid, @objname output
    IF @objname IS NULL
        return 1
    if (@colid is NULL)
    begin
        insert into @coltable (name, column_id, is_computed)
            select sc.name, sc.column_id, sc.is_computed
            from sys.columns as sc join sysarticlecolumns as sac
            on sc.column_id = sac.colid
            WHERE sc.object_id = @tabid
                and sac.artid = @artid
        
        -- Check if any columns computed
        
        if exists (select * from sys.columns where object_id = @tabid and is_computed = 1)
        begin
            
            -- Check if any PK column is computed
            
            if (sys.fn_ispkcomputed(@tabid) = 1)
            begin
                
                -- add base column for any primary computed keys
                
                insert into @coltable (name, column_id, is_computed)
                select sc.name, sc.column_id, sc.is_computed
                from sys.columns as sc
                    join
                    (
                        select bc.column_id
                        from sys.fn_basecolsofcomputedpkcol(@tabid, NULL) as bc
                        where bc.column_id not in (select column_id from sysarticlecolumns where artid = @artid)
                    ) as pkb
                    on sc.column_id = pkb.column_id
                WHERE sc.object_id = @tabid
            end -- PK has computed cols
        end -- exists computed cols
    end -- colid is null
    else
    begin
        
        -- colid was specified
        
        insert into @coltable (name, column_id, is_computed)
            select sc.name, sc.column_id, sc.is_computed
            from sys.columns as sc join sysarticlecolumns as sac
            on sc.column_id = sac.colid
            WHERE sc.object_id = @tabid
                and sc.column_id = @colid
                and sac.artid = @artid
        if exists (select * from @coltable where is_computed = 1)
        begin
            
            -- add base column for this computed column if this is part of PK
            
            insert into @coltable (name, column_id, is_computed)
            select sc.name, sc.column_id, sc.is_computed
            from sys.columns as sc
                join
                (
                    select bc.column_id
                    from sys.fn_basecolsofcomputedpkcol(@tabid, @colid) as bc
                    where bc.column_id not in (select column_id from sysarticlecolumns where artid = @artid)
                ) as pkb
                on sc.column_id = pkb.column_id
            where sc.object_id = @tabid
        end
    end
    -- loop through the selected columns
    DECLARE #hCartcol CURSOR LOCAL FAST_FORWARD FOR
        SELECT name, column_id FROM @coltable


	

    -- begin transaction
    BEGIN TRANSACTION
    save tran sp_MSarticlecol
    -- open and enumerate the cursor
    OPEN #hCartcol
    FETCH #hCartcol INTO @colname, @colid
    WHILE (@@fetch_status <> -1)
    BEGIN

       IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
       BEGIN
          IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
          BEGIN
            EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplicated(Value = 1)
            IF @@ERROR <> 0
            BEGIN
                RAISERROR(15165, -1, -1, @objname)
                GOTO ERROR_ABORT_EXIT
            END
            --for tables with has_replication_filter on (see sp_MSsetfilteredstatus),
            --set all replicated column (except legacy blob) as part of PK so SE logs
            --old value for deletes
            --this is done inside sp_MSsetfilteredstatus anayway but set it here anyway
            --so that we don't depand on the order of how sp_MSsetfilteredstatus and sp_articlecolumn gets called
            if exists (select * from sys.tables where object_id = @tabid
                                                                and has_replication_filter = 1)
                and not exists (select * from sys.columns where object_id = @tabid
                                                                                and column_id = @colid
                                                                                and (system_type_id in (34, 35, 99)
                                                                                or user_type_id in (34, 35, 99))) -- image, text, ntext
            begin
                EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetLogForRepl(Value = 1)
                IF @@ERROR <> 0
                BEGIN
                    RAISERROR(15052, -1, -1, @objname)
                    GOTO ERROR_ABORT_EXIT
                END
            end
          END
          ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'nonsqlsub'
          BEGIN
            EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplNonSQLSub(Value = 1)
            IF @@ERROR <> 0
            BEGIN
                RAISERROR(15052, -1, -1, @objname)
                GOTO ERROR_ABORT_EXIT
            END
            IF @allow_dts = 1
            BEGIN
                EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetDTSReplicated(Value = 1)
                IF @@ERROR <> 0
                   BEGIN
                   RAISERROR(15052, -1, -1, @objname)
                   GOTO ERROR_ABORT_EXIT
                END
            END
          END
        END
        ELSE /* drop */
        BEGIN
            /*
            ** Is there another non-sql server subscription on the column?
            ** Or another article publishing the column?
            */
            EXEC @retcode = sys.sp_MSarticlecolstatus @artid = @artid, @tabid = @tabid, @colid = @colid, @type = @type, @status = @status OUTPUT
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                GOTO ERROR_ABORT_EXIT
            END
	
            IF (@status = 0)
            BEGIN
                IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
                BEGIN
                    /* Clear 'publish' bit */
                    EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplicated(Value = 0)
                    IF @@ERROR <> 0
                    BEGIN
                        RAISERROR(15052, -1, -1, @objname)
                        GOTO ERROR_ABORT_EXIT
                    END

                    EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetLogForRepl(Value = 0)
                    IF @@ERROR <> 0
                    BEGIN
                        RAISERROR(15052, -1, -1, @objname)
                        GOTO ERROR_ABORT_EXIT
                    END
                END
                ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'nonsqlsub'
                BEGIN
                    /* Clear 'non-sql server subscription' bit */
                    EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplNonSQLSub(Value = 0)
                    IF @@ERROR <> 0
                    BEGIN
                       RAISERROR(15052, -1, -1, @objname)
                       GOTO ERROR_ABORT_EXIT
                    END

                    if @allow_dts = 1
                    BEGIN
                        EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetDTSReplicated(Value = 0)
                        IF @@ERROR <> 0
                        BEGIN
                           RAISERROR(15052, -1, -1, @objname)
                           GOTO ERROR_ABORT_EXIT
                        END
                    END
                END
            END
        END
        -- fetch next column
        FETCH #hCartcol INTO @colname, @colid
    END
    CLOSE #hCartcol
    DEALLOCATE #hCartcol

    COMMIT TRANSACTION

    RETURN (0)

ERROR_ABORT_EXIT:
    ROLLBACK TRAN sp_MSarticlecol
    COMMIT TRAN
    RETURN (1)
end

 
Last revision 2008RTM
See also

  sp_MSarticlecolstatus (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_articlecolumn (Procedure)
sp_MSrepl_changesubstatus (Procedure)
sp_MSrepl_enable_articles_for_het_sub (Procedure)
sp_vupgrade_syscol_status (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