sp_addarticle Replication Stored Procedure

Creates an article and adds it to a publication.

Syntax

sp_addarticle publication, article, source_table [, destination_table]
    [, vertical_partition] [, type] [, filter] [, sync_object] [, ins_cmd]
    [, del_cmd] [, upd_cmd] [, creation_script] [, description]
    [, pre_creation_cmd] [, filter_clause]

where

publication

Specifies the name of the publication that will contain the article. This name must be unique in the database.

article

Is the article's name. The name must be unique within the publication.

source_table

Specifies the name of the underlying table represented by the article.

destination_table

Is the name of the destination (subscription) table, if different from source_table.

vertical_partition

Determines the vertical position. If set to false, indicates there is no vertical partition, will publish all columns. If set to true, clears all columns except the declared primary key. Columns are added using the sp_articlecolumn replication stored procedure.

type

Specifies the type of article. Can be:
1 Log-based article (the default)
3 Log-based article with manual filter
5 Log based article with manual view
7 Log-based article with manual filter and manual view

filter

Specifies the stored procedure (created with FOR REPLICATION) used to filter the table (horizontal partition). The default is NULL, which means that sp_articlefilter will be called to automatically generate the filter. This occurs after adding any columns with sp_articlecolumn. If not NULL, the filter procedure will not be created (assumes manual stored procedure creation).

sync_object

Is the name of the table or view used for producing a synchronization output file. The default is NULL, which means that sp_articleview will be called to automatically create the view that will synchronize the output file. This occurs after adding any columns with sp_articlecolumn. If not NULL, a view will not be created (assumes manual view creation).

ins_cmd

Specifies the command to execute upon insert; otherwise, the insert is constructed from the log. The default is SQL.

del_cmd

Specifies the command to execute upon delete; otherwise, the delete is constructed from the log. The default is SQL.

upd_cmd

Specifies command to execute upon update; otherwise, the update is constructed from the log. The default is SQL.

creation_script

Is the path and name of an article schema script used to create target tables. The default is NULL.

description

Is an optional descriptive entry for the article.

pre-creation_cmd

Is a pre-creation command for drop table, delete table, or truncate. Can be:

0 None
1 Drop (the default)
2 Delete
3 Truncate

filter_clause

Is a WHERE clause specifying the horizontal partitioning.

Remarks

If vertical_partition is false and filter_clause is not NULL, a view is created and a filter procedure on the source table using the filter_clause is created. If vertical_partition is true, sp_addarticle defers the creation of the view until sp_articleview is called (after the last sp_articlecolumn is added).

Example

This example creates the article authors and adds it to the authors_publication publication.

sp_addarticle authors_publication, authors, authors, 
@creation_script='c:\sql95\repldata\authors.sch'
@description='Authors Table Article'

Permission

Execute permission defaults to the database owner and the system administrator.

Tables Used

sysobjects, sysarticles, syspublications

See Also