sp_addarticle (Transact-SQL)

Creates an article and adds it to a publication. This stored procedure is executed at the Publisher on the publication database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_addarticle [ @publication = ] 'publication' 
        , [ @article = ] 'article' 
    [ , [ @source_table = ] 'source_table' ]
    [ , [ @destination_table = ] 'destination_table' ] 
    [ , [ @vertical_partition = ] 'vertical_partition' ] 
    [ , [ @type = ] 'type' ] 
    [ , [ @filter = ] 'filter' ] 
    [ , [ @sync_object= ] 'sync_object' ] 
    [ , [ @ins_cmd = ] 'ins_cmd' ] 
    [ , [ @del_cmd = ] 'del_cmd' ] 
    [ , [ @upd_cmd = ] 'upd_cmd' ] 
    [ , [ @creation_script = ] 'creation_script' ] 
    [ , [ @description = ] 'description' ] 
    [ , [ @pre_creation_cmd = ] 'pre_creation_cmd' ] 
    [ , [ @filter_clause = ] 'filter_clause' ] 
    [ , [ @schema_option = ] schema_option ] 
    [ , [ @destination_owner = ] 'destination_owner' ] 
    [ , [ @status = ] status ] 
    [ , [ @source_owner = ] 'source_owner' ] 
    [ , [ @sync_object_owner = ] 'sync_object_owner' ] 
    [ , [ @filter_owner = ] 'filter_owner' ] 
    [ , [ @source_object = ] 'source_object' ] 
    [ , [ @artid = ] article_ID  OUTPUT ] 
    [ , [ @auto_identity_range = ] 'auto_identity_range' ] 
    [ , [ @pub_identity_range = ] pub_identity_range ] 
    [ , [ @identity_range = ] identity_range ] 
    [ , [ @threshold = ] threshold ] 
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @use_default_datatypes = ] use_default_datatypes
    [ , [ @identityrangemanagementoption = ] identityrangemanagementoption ]
    [ , [ @publisher = ] 'publisher' ]
    [ , [ @fire_triggers_on_snapshot = ] 'fire_triggers_on_snapshot' ] 

Arguments

  • [ @publication = ] 'publication'
    Is the name of the publication that contains the article. The name must be unique in the database. publication is sysname, with no default.
  • [ @article = ] 'article'
    Is the name of the article. The name must be unique within the publication. article is sysname, with no default.
  • [ @source_table = ] 'source_table'
    This parameter has been deprecated and is supported for backward compatibility of scripts; use source_object instead. This parameter is not supported for Oracle Publishers.
  • [ @destination_table = ] 'destination_table'
    Is the name of the destination (subscription) table, if different from source_tableor the stored procedure. destination_table is sysname, with a default of NULL, which means that source_table equals destination_table.
  • [ @vertical_partition = ] 'vertical_partition'
    Enables and disables column filtering on a table article. vertical_partition is nchar(5), with a default of FALSE.

    false indicates there is no vertical filtering and publishes all columns.

    true clears all columns except the declared primary key, nullable columns with no default, and unique key columns. Columns are added using sp_articlecolumn.

  • [ @type = ] 'type'
    Is the type of article. type is sysname, and can be one of the following values.

    Value Description

    aggregate schema only

    Aggregate function with schema only.

    func schema only

    Function with schema only.

    indexed view logbased

    Log-based indexed view article. Not supported for Oracle Publishers. For this type of article, the base table does not need to be published separately.

    indexed view logbased manualboth

    Log-based indexed view article with manual filter and manual view. This option requires that you specify both sync_object and filter parameters. For this type of article, the base table does not need to be published separately. Not supported for Oracle Publishers.

    indexed view logbased manualfilter

    Log-based indexed view article with manual filter. This option requires that you specify both sync_object and filter parameters. For this type of article, the base table does not need to be published separately. Not supported for Oracle Publishers.

    indexed view logbased manualview

    Log-based indexed view article with manual view. This option requires that you specify the sync_object parameter. For this type of article, the base table does not need to be published separately. Not supported for Oracle Publishers.

    indexed view schema only

    Indexed view with schema only. For this type of article, the base table must also be published.

    logbased (default)

    Log-based article.

    logbased manualboth

    Log-based article with manual filter and manual view. This option requires that you specify both sync_object and filter parameters. Not supported for Oracle Publishers.

    logbased manualfilter

    Log-based article with manual filter. This option requires that you specify both sync_object and filter parameters. Not supported for Oracle Publishers.

    logbased manualview

    Log-based article with manual view. This option requires that you specify the sync_object parameter. Not supported for Oracle Publishers.

    proc exec

    Replicates the execution of the stored procedure to all Subscribers of the article. Not supported for Oracle Publishers.

    proc schema only

    Procedure with schema only. Not supported for Oracle Publishers.

    serializable proc exec

    Replicates the execution of the stored procedure only if it is executed within the context of a serializable transaction. Not supported for Oracle Publishers.

    view schema only

    View with schema only. Not supported for Oracle Publishers. When using this option, you must also publish the base table.

  • [ @filter = ] 'filter'
    Is the stored procedure (created with FOR REPLICATION) used to filter the table horizontally. filter is nvarchar(386), with a default of NULL. sp_articleview and sp_articlefilter must be executed manually to create the view and filter stored procedure. If not NULL, the filter procedure is not created (assumes the stored procedure is created manually).
  • [ @sync_object = ] 'sync_object'
    Is the name of the table or view used for producing the data file used to represent the snapshot for this article. sync_object is nvarchar(386), with a default of NULL. If NULL, sp_articleview is called to automatically create the view used to generate the output file. This occurs after adding any columns with sp_articlecolumn. If not NULL, a view is not created (assumes the view is manually created).
  • [ @ins_cmd = ] 'ins_cmd'
    Is the replication command type used when replicating inserts for this article. ins_cmd is nvarchar(255), and can be one of the following values.

    Value Description

    NONE

    No action is taken.

    CALL sp_MSins_table (default)

    -or-

    CALL custom_stored_procedure_name

    Calls a stored procedure to be executed at the Subscriber. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. custom_stored_procedure is the name of a user-created stored procedure. sp_MSins_table contains the name of the destination table in place of the _table part of the parameter. When destination_owner is specified, it is prepended to the destination table name. For example, for the ProductCategory table owned by the Production schema at the Subscriber, the parameter would be CALL sp_MSins_ProductionProductCategory. For an article in a peer-to-peer replication topology, _table is appended with a GUID value. Specifying custom_stored_procedure is not supported for updating subscribers.

    SQL or NULL

    Replicates an INSERT statement. The INSERT statement is provided values for all columns published in the article. This command is replicated on inserts:

    INSERT INTO <table name> VALUES (c1value, c2value, c3value, ..., cnvalue)

    For more information, see Specifying How Changes Are Propagated for Transactional Articles.

  • [ @del_cmd =] 'del_cmd'
    Is the replication command type used when replicating deletes for this article. del_cmd is nvarchar(255), and can be one of the following values.

    Value Description

    NONE

    No action is taken.

    CALLsp_MSdel_table (default)

    -or-

    CALL custom_stored_procedure_name

    Calls a stored procedure to be executed at the Subscriber. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. custom_stored_procedure is the name of a user-created stored procedure. sp_MSdel_table contains the name of the destination table in place of the _table part of the parameter. When destination_owner is specified, it is prepended to the destination table name. For example, for the ProductCategory table owned by the Production schema at the Subscriber, the parameter would be CALL sp_MSdel_ProductionProductCategory. For an article in a peer-to-peer replication topology, _table is appended with a GUID value. Specifying custom_stored_procedure is not supported for updating subscribers.

    XCALL sp_MSdel_table

    -or-

    XCALL custom_stored_procedure_name

    Calls a stored procedure taking XCALL style parameters. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. Specifying a user-created stored procedure is not allowed for updating subscribers.

    SQL or NULL

    Replicates a DELETE statement. The DELETE statement is provided all primary key column values. This command is replicated on deletes:

    DELETE FROM <table name> WHERE pkc1 = pkc1value AND pkc2 = pkc2value AND pkcn = pkcnvalue

    For more information, see Specifying How Changes Are Propagated for Transactional Articles.

  • [ @upd_cmd =] 'upd_cmd'
    Is the replication command type used when replicating updates for this article. upd_cmd is nvarchar(255), and can be one of the following values.

    Value Description

    NONE

    No action is taken.

    CALL sp_MSupd_table

    -or-

    CALL custom_stored_procedure_name

    Calls a stored procedure to be executed at the Subscriber. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article.

    MCALL sp_MSupd_table

    -or-

    MCALL custom_stored_procedure_name

    Calls a stored procedure taking MCALL style parameters. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. custom_stored_procedure is the name of a user-created stored procedure. sp_MSupd_table contains the name of the destination table in place of the _table part of the parameter. When destination_owner is specified, it is prepended to the destination table name. For example, for the ProductCategory table owned by the Production schema at the Subscriber, the parameter would be MCALL sp_MSupd_ProductionProductCategory. For an article in a peer-to-peer replication topology, _table is appended with a GUID value. Specifying a user-created stored procedure is not allowed for updating subscribers.

    SCALL sp_MSupd_table (default)

    -or-

    SCALL custom_stored_procedure_name

    Calls a stored procedure taking SCALL style parameters. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. custom_stored_procedure is the name of a user-created stored procedure. sp_MSupd_table contains the name of the destination table in place of the _table part of the parameter. When destination_owner is specified, it is prepended to the destination table name. For example, for the ProductCategory table owned by the Production schema at the Subscriber, the parameter would be SCALL sp_MSupd_ProductionProductCategory. For an article in a peer-to-peer replication topology, _table is appended with a GUID value. Specifying a user-created stored procedure is not allowed for updating subscribers.

    XCALL sp_MSupd_table

    -or-

    XCALL custom_stored_procedure_name

    Calls a stored procedure taking XCALL style parameters. To use this method of replication, use schema_option to specify automatic creation of the stored procedure, or create the specified stored procedure in the destination database of each Subscriber of the article. Specifying a user-created stored procedure is not allowed for updating subscribers.

    SQL or NULL

    Replicates an UPDATE statement. The UPDATE statement is provided on all column values and the primary key column values. This command is replicated on updates:

    UPDATE <table name> SET c1 = c1value, SET c2 = c2value, SET cn = cnvalue WHERE pkc1 = pkc1value AND pkc2 = pkc2value AND pkcn = pkcnvalue

    Note

    The CALL, MCALL, SCALL, and XCALL syntax vary the amount of data propagated to the subscriber. The CALL syntax passes all values for all inserted and deleted columns. The SCALL syntax passes values only for affected columns. The XCALL syntax passes values for all columns, whether changed or not, including the previous value of the column. For more information, see Specifying How Changes Are Propagated for Transactional Articles.

  • [ @creation_script =] 'creation_script'
    Is the path and name of an optional article schema script used to create the article in the subscription database. creation_script is nvarchar(255), with a default of NULL.
  • [ @description =] 'description'
    Is a descriptive entry for the article. description is nvarchar(255), with a default of NULL.
  • [ @pre_creation_cmd =] 'pre_creation_cmd'
    Specifies what the system should do if it detects an existing object of the same name at the subscriber when applying the snapshot for this article. pre_creation_cmd is nvarchar(10), and can be one of the following values.

    Value Description

    none

    Does not use a command.

    delete

    Deletes data from the destination table before applying the snapshot. When the article is horizontally filtered, only data in columns specified by the filter clause is deleted. Not supported for Oracle Publishers when a horizontal filter is defined.

    drop (default)

    Drops the destination table.

    truncate

    Truncates the destination table. Is not valid for ODBC or OLE DB Subscribers.

  • [ @filter_clause=] 'filter_clause'
    Is a restriction (WHERE) clause that defines a horizontal filter. When entering the restriction clause, omit the keyword WHERE. filter_clause is ntext, with a default of NULL. For more information, see Filtering Published Data.
  • [ @schema_option =] schema_option
    Is a bitmask of the schema generation option for the given article. schema_option is binary(8), and can be the | (Bitwise OR) product of one or more of these values:

    Note

    If this value is NULL, the system auto-generates a valid schema option for the article depending on other article properties. The Default Schema Options table given in the Remarks shows the value that will be chosen based upon the combination of the article type and the replication type.

    Value

  • [ @destination_owner =] 'destination_owner'
    Is the name of the owner of the destination object. destination_owner is sysname, with a default of NULL. When destination_owner is not specified, the owner is specified automatically based on the following rules:

    Condition Destination object owner

    Publication uses native-mode bulk copy to generate the initial snapshot, which only supports SQL Server Subscribers.

    Defaults to the value of source_owner.

    Published from a non-SQL Server Publisher.

    Defaults to the owner of the destination database.

    Publication uses character-mode bulk copy to generate the initial snapshot, which supports non-SQL Server Subscribers.

    Not assigned.

    To support non-SQL Server Subscribers, destination_owner must be NULL.

  • [ @status=] status
    Specifies if the article is active and additional options for how changes are propagated. status is tinyint, and can be the | (Bitwise OR) product of one or more of these values.

    Value

  • [ @source_owner =] 'source_owner'
    Is the owner of the source object. source_owner is sysname, with a default of NULL. source_owner must be specified for Oracle Publishers.
  • [ @sync_object_owner =] 'sync_object_owner'
    Is the owner of the view that defines the published article. sync_object_owner is sysname, with a default of NULL.
  • [ @filter_owner =] 'filter_owner'
    Is the owner of the filter. filter_owner is sysname, with a default of NULL.
  • [ @source_object =] 'source_object'
    Is the database object to be published. source_object is sysname, with a default of NULL. If source_table is NULL, source_object cannot be NULL.source_object should be used instead of source_table. For more information about the types of objects that can be published using snapshot or transactional replication, see Publishing Data and Database Objects.
  • [ @artid = ] article_ID OUTPUT
    Is the article ID of the new article. article_ID is int with a default of NULL, and it is an OUTPUT parameter.
  • [ @auto_identity_range = ] 'auto_identity_range'
    Enables and disables automatic identity range handling on a publication at the time it is created. auto_identity_range is nvarchar(5), and can be one of the following values:

    Value Description

    true

    Enables automatic identity range handling

    false

    Disables automatic identity range handling

    NULL(default)

    Identity range handling is set by identityrangemanagementoption.

    Note

    auto_identity_range has been deprecated and is provided for backward compatibility only. You should use identityrangemanagementoption for specifying identity range management options. For more information, see Replicating Identity Columns.

  • [ @pub_identity_range = ] pub_identity_range
    Controls the range size at the Publisher if the article has identityrangemanagementoption set to auto or auto_identity_range set to true. pub_identity_range is bigint, with a default of NULL. Not supported for Oracle Publishers.
  • [ @identity_range = ] identity_range
    Controls the range size at the Subscriber if the article has identityrangemanagementoption set to auto or auto_identity_range set to true. identity_range is bigint, with a default of NULL. Used when auto_identity_range is set to true. Not supported for Oracle Publishers.
  • [ @threshold = ] threshold
    Is the percentage value that controls when the Distribution Agent assigns a new identity range. When the percentage of values specified in threshold is used, the Distribution Agent creates a new identity range. threshold is bigint, with a default of NULL. Used when identityrangemanagementoption is set to auto or auto_identity_range is set to true. Not supported for Oracle Publishers.
  • [ @force_invalidate_snapshot = ] force_invalidate_snapshot
    Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.

    0 specifies that adding an article does not cause the snapshot to be invalid. If the stored procedure detects that the change requires a new snapshot, an error occurs and no changes are made.

    1 specifies that adding an article may cause the snapshot to be invalid, and if subscriptions exist that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot to be generated.

  • [ @use_default_datatypes = ] use_default_datatypes
    Is whether the default column data type mappings are used when publishing an article from an Oracle Publisher. use_default_datatypes is bit, with a default of 1.

    1 = the default article column mappings are used. The default data type mappings can be displayed by executing sp_getdefaultdatatypemapping.

    0 = custom article column mappings are defined, and therefore sp_articleview is not called by sp_addarticle.

    When use_default_datatypes is set to 0, you must execute sp_changearticlecolumndatatype once for each column mapping being changed from the default. After all custom column mappings have been defined, you must execute sp_articleview.

    Note

    This parameter should only be used for Oracle Publishers. Setting use_default_datatypes to 0 for a SQL Server Publisher generates an error.

  • [ @identityrangemanagementoption = ] identityrangemanagementoption
    Specifies how identity range management is handled for the article. identityrangemanagementoption is nvarchar(10), and can be one of the following values.

    Value Description

    none

    Replication does no explicit identity range management. This option is recommended only for backwards compatibility with earlier versions of SQL Server. Not allowed for peer replication.

    manual

    Marks the identity column using NOT FOR REPLICATION to enable manual identity range handling.

    auto

    Specifies automatic management of identity ranges.

    NULL(default)

    Defaults to none when the value of auto_identity_range is not true. Defaults to manual in a peer-to-peer topology default (auto_identity_range is ignored).

    For backward compatibility, when the value of identityrangemanagementoption is NULL, the value of auto_identity_range is checked. However, when the value of identityrangemanagementoption is not NULL, then the value of auto_identity_range is ignored*.*

    For more information, see Replicating Identity Columns.

  • [ @publisher = ] 'publisher'
    Specifies a non-SQL Server Publisher. publisher is sysname, with a default of NULL.

    Note

    publisher should not be used when adding an article to a SQL Server Publisher.

  • [ @fire_triggers_on_snapshot = ] 'fire_triggers_on_snapshot'
    Is if replicated user triggers are executed when the initial snapshot is applied. fire_triggers_on_snapshot is nvarchar(5), with a default of FALSE. true means that user triggers on a replicated table are executed when the snapshot is applied. In order for triggers to be replicated, the bitmask value of schema_option must include the value 0x100.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addarticle is used in snapshot replication or transactional replication.

By default, replication does not publish any columns in the source table when the column data type is not supported by replication. If you need to publish such a column, you must execute sp_articlecolumn to add the column. For more information, see Considerations for All Types of Replication.

When adding an article to a publication that supports peer-to-peer transactional replication, the following restrictions apply:

  • Parameterized statements must be specified for all logbased articles. You must include 16 in the status value.
  • Name and owner of the destination table must match the source table.
  • The article cannot be filtered horizontally or vertically.
  • Automatic identity range management is not supported. You must specify a value of manual for identityrangemanagementoption.
  • If a timestamp column exists in the table, you must include 0x08 in schema_option to replicate the column as timestamp.
  • A value of SQL cannot be specified for ins_cmd, upd_cmd, and del_cmd.

For more information, see Peer-to-Peer Transactional Replication.

When you publish objects, their definitions are copied to Subscribers. If you are publishing a database object that depends on one or more other objects, you must publish all referenced objects. For example, if you publish a view that depends on a table, you must publish the table also.

If vertical_partition is set to true, sp_addarticle defers the creation of the view until sp_articleview is called (after the last sp_articlecolumn is added).

If the publication allows updating subscriptions and the published table does not have a uniqueidentifier column, sp_addarticle adds a uniqueidentifier column to the table automatically.

For articles supporting transformable subscriptions, the values specified for ins_cmd, del_cmdand upd_cmd are ignored.

Default Schema Options

This table describes the default value set by replication if schema_options is not specified by the user, where this value depends on the replication type (shown across the top) and the article type (shown down the first column).

Article Type Replication Type  

 

Transactional

Snapshot

aggregate schema only

0x01

0x01

func schema only

0x01

0x01

indexed view schema only

0x01

0x01

indexed view logbased

0x30F3

0x3071

indexed view logbase manualboth

0x30F3

0x3071

indexed view logbased manualfilter

0x30F3

0x3071

indexed view logbased manualview

0x30F3

0x3071

logbased

0x30F3

0x3071

logbased manualfilter

0x30F3

0x3071

logbased manualview

0x30F3

0x3071

proc exec

0x01

0x01

proc schema only

0x01

0x01

serialized proc exec

0x01

0x01

view schema only

0x01

0x01

Note

If a publication is enabled for queued updating, a schema_option value of 0x80 is added to the default value shown in the table. The default schema_option for a non-SQL Server publication is 0x050D3.

Valid Schema Options

This table describes the allowable values of schema_option based upon the replication type (shown across the top) and the article type (shown down the first column).

Article type Replication type  

 

Transactional

Snapshot

logbased

All options

All options but 0x02

logbased manualfilter

All options

All options but 0x02

logbased manualview

All options

All options but 0x02

indexed view logbased

All options

All options but 0x02

indexed view logbased manualfilter

All options

All options but 0x02

indexed view logbased manualview

All options

All options but 0x02

indexed view logbase manualboth

All options

All options but 0x02

proc exec

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

serialized proc exec

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

proc schema only

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

view schema only

0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000

0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000

func schema only

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

0x01, 0x20, 0x2000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x10000000, 0x20000000, 0x40000000, and 0x80000000

indexed view schema only

0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000

0x01, 0x010, 0x020, 0x040, 0x0100, 0x2000, 0x40000, 0x100000, 0x200000, 0x400000, 0x800000, 0x2000000, 0x8000000, 0x40000000, and 0x80000000

Note

For queued updating publications, the schema_option values of 0x8000 and 0x80 must be enabled. The supported schema_option values for non-SQL Server publications are: 0x01, 0x02, 0x10, 0x40, 0x80, 0x1000, 0x4000 and 0X8000.

Permissions

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

Example

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

See Also

Reference

sp_articlecolumn (Transact-SQL)
sp_articlefilter (Transact-SQL)
sp_articleview (Transact-SQL)
sp_changearticle (Transact-SQL)
sp_droparticle (Transact-SQL)
sp_helparticle (Transact-SQL)
sp_helparticlecolumns (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

How to: Define an Article (Replication Transact-SQL Programming)
Publishing Data and Database Objects

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

Changed content:
  • Updated the description of the 0x20000000 value of the schema_option parameter.

5 December 2005

New content:
  • Documented additional restrictions when setting the 0x20 option for the schema_option parameter.
Changed content:
  • Clarified descriptions of the supported options for the type parameter.
  • Corrected the size of the creation_script parameter.