sp_articlecolumn (Transact-SQL)

  • Article

Applies to: SQL Server

Used to specify columns included in an article to vertically filter data in a published table. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sp_articlecolumn
    [ @publication = ] N'publication'
    , [ @article = ] N'article'
    [ , [ @column = ] N'column' ]
    [ , [ @operation = ] N'operation' ]
    [ , [ @refresh_synctran_procs = ] refresh_synctran_procs ]
    [ , [ @ignore_distributor = ] ignore_distributor ]
    [ , [ @change_active = ] change_active ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]
    [ , [ @publisher = ] N'publisher' ]
    [ , [ @internal = ] internal ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication that contains this article. @publication is sysname, with no default.

[ @article = ] N'article'

The name of the article. @article is sysname, with no default.

[ @column = ] N'column'

The name of the column to be added or dropped. @column is sysname, with a default of NULL. If NULL, all columns are published.

[ @operation = ] N'operation'

Specifies whether to add or drop columns in an article. @operation is nvarchar(5), with a default of add.

  • add marks the column for replication.
  • drop unmarks the column.

[ @refresh_synctran_procs = ] refresh_synctran_procs

Specifies whether the stored procedures supporting immediate updating subscriptions are regenerated to match the number of columns replicated. @refresh_synctran_procs is bit, with a default of 1. If 1, the stored procedures are regenerated.

[ @ignore_distributor = ] ignore_distributor

Indicates if this stored procedure executes without connecting to the Distributor. @ignore_distributor is bit, with a default of 0.

  • If 0, the database must be enabled for publishing, and the article cache should be refreshed to reflect the new columns replicated by the article.
  • If 1, allows article columns to be dropped for articles that reside in an unpublished database; should be used only in recovery situations.

[ @change_active = ] change_active

Allows modifying the columns in publications that have subscriptions. @change_active is int, with a default of 0.

  • If 0, columns aren't modified.
  • If 1, columns can be added or dropped from active articles that have subscriptions.

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

Acknowledges that the action taken by this stored procedure might invalidate an existing snapshot. @force_invalidate_snapshot is bit, with a default of 0.

  • 0 specifies that changes to the article don't cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.
  • 1 specifies that changes to the article might cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

[ @force_reinit_subscription = ] force_reinit_subscription

Acknowledges that the action taken by this stored procedure might require existing subscriptions to be reinitialized. @force_reinit_subscription is bit, with a default of 0.

  • 0 specifies that changes to the article don't cause the subscription to be reinitialized. If the stored procedure detects that the change would require subscriptions to be reinitialized, an error occurs and no changes are made.
  • 1 specifies that changes to the article cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

[ @publisher = ] N'publisher'

Specifies a non-SQL Server Publisher. @publisher is sysname, with a default of NULL.

@publisher shouldn't be used with a SQL Server Publisher.

[ @internal = ] internal

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

Remarks

sp_articlecolumn is used in snapshot replication and transactional replication.

Only an unsubscribed article can be filtered using sp_articlecolumn.

Examples

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran'; 
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL'; 
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';

-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema 
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle 
    @publication = @publication, 
    @article = @table, 
    @source_object = @table,
    @source_owner = @schemaowner, 
    @schema_option = 0x80030F3,
    @vertical_partition = N'true', 
    @type = N'logbased',
    @filter_clause = @filterclause;

-- (Optional) Manually call the stored procedure to create the 
-- horizontal filtering stored procedure. Since the type is 
-- 'logbased', this stored procedures is executed automatically.
EXEC sp_articlefilter 
    @publication = @publication, 
    @article = @table, 
    @filter_clause = @filterclause, 
    @filter_name = @filtername;

-- Add all columns to the article.
EXEC sp_articlecolumn 
    @publication = @publication, 
    @article = @table;

-- Remove the DaysToManufacture column from the article
EXEC sp_articlecolumn 
    @publication = @publication, 
    @article = @table, 
    @column = N'DaysToManufacture', 
    @operation = N'drop';

-- (Optional) Manually call the stored procedure to create the 
-- vertical filtering view. Since the type is 'logbased', 
-- this stored procedures is executed automatically.
EXEC sp_articleview 
    @publication = @publication, 
    @article = @table,
    @filter_clause = @filterclause;
GO

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_articlecolumn.

Related content