sp_addarticle (T-SQL)

Creates an article and adds it to a publication.

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']

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 article’s name. The name must be unique within the publication. article is sysname, with no default.

[@source_table =] 'source_table'

Is the name of the underlying table represented by the article or stored procedure. source_table is nvarchar(386), and must be local, conform to the rules for identifiers, and be a table (not a view or another database object). source_table is supported for backward compatibility only; use source_object instead.

[@destination_table =] 'destination_table'

Is the name of the destination (subscription) table, if different from source_table or the stored procedure. destination_table is sysname, with a default of NULL, which means that source_table equals destination_table.

[@vertical_partition =] 'vertical_partition'

Is the column(s) to replicate. 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. Columns are added using sp_articlecolumn.

[@type =] 'type'

Is the type of article. type is sysname, and can be one of these values.

 

Value	Description
logbased	Log-based article.
logbased manualfilter	Log-based article with manual filter.
logbased manualview	Log-based article with manual view.
logbased manualboth	Log-based article with manual filter and manual view.
proc exec	Replicates the execution of the stored procedure to all Subscribers of the article.
serializable proc exec	Replicates the execution of the stored procedure only if it is executed within the context of a serializable transaction.
NULL (default)

[@filter =] 'filter'

Is the stored procedure (created with FOR REPLICATION) used to horizontally filter the table. 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 manually created).

[@sync_object =] 'sync_object'

Is the name of the table or view used for producing a synchronization output file. sync_object is nvarchar(386), with a default of NULL. If NULL sp_articleview is called to automatically create the view that synchronizes 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 mechanism used when replicating inserts. ins_cmd is nvarchar(255), and can be one of these values.

 

Value	Description
NONE	No action is taken.
CALL sp_MSins_article (default)	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.
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)

[@del_cmd =] 'del_cmd'

Is the replication mechanism used when replicating deletes. del_cmd is nvarchar(255), and can be one of these values.

 

Value	Description
NONE	No action is taken.
CALL sp_MSdel_article (default)	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.
XCALL sp_MSdel_article	Calls a stored procedure taking XCALL style parameters. For more information, see Using Custom Stored Procedures in Articles.
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

[@upd_cmd =] 'upd_cmd'

Is the replication mechanism used when replicating updates. upd_cmd is nvarchar(255), and can be one of these values.

 

Value	Description
NONE	No action is taken.
CALL sp_MSupd_article	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_article (default)	Calls a stored procedure taking MCALL style parameters. For an example, see sp_scriptmappedupdproc.
XCALL sp_MSupd_article	Calls a stored procedure taking XCALL style parameters. For more information, see Using Custom Stored Procedures in Articles.
SQL or NULL	Replicates an UPDATE statement. The UPDATE statement is provided 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

[@creation_script =] 'creation_script'

Is the path and name of an article schema script used to create target tables. creation_script is nvarchar(127), 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'

Is a precreation command for the DROP TABLE, DELETE TABLE, or TRUNCATE TABLE statement. pre_creation_cmd is nvarchar(10), and can be one of these values.

 

Value	Description
none	Does not use a command.
delete	Deletes the destination table.
drop (default)	Drops the destination table.
truncate	Truncates the destination table. Is not valid for ODBC or OLE BD subscribers.

[@filter_clause =] 'filter_clause'

Is a WHERE clause that specifies the horizontal partitioning. filter_clause is ntext, with a default of NULL.

[@schema_option =] schema_option

Is a bitmask of the schema generation option for the given article. schema_option is binary(8), and can be a combination of these values.

 

Value	Description
0x0000000000000000	Disables scripting by the Snapshot Agent and uses the provided CreationScript.
0x0000000000000001	Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on).
0x0000000000000002	Generates custom stored procedures for the article if defined.
0x0000000000000010	Generates a corresponding clustered index.
0x0000000000000020	Converts user-defined data types to base data types.
0x0000000000000040	Generates corresponding nonclustered index(es).
0x0000000000000080	Includes declared referential integrity on the primary keys.
0x0000000000000073 (default)	Generates the CREATE TABLE statement, creates clustered index, creates nonclustered index(es), converts user-defined data types to base data types, and generates custom stored procedure scripts to be applied at the Subscriber.
0x0000000000000001	Default for stored procedure articles.

[@destination_owner =] 'destination_owner'

Is the name of the owner of the destination object. destination_owner is sysname, with a default of NULL. If the publication can be subscribed to by ODBC Subscribers, destination_owner must be NULL.

[@status =] status

Is the bitmask of the article options. status is tinyint, and can be one of these values.

 

Value	Description
0	No additional properties.
8	Include the column name in INSERT statements.
16 (default)	Use parameterized statements.
24	Both include the column name in INSERT statements and use parameterized statements.

[@source_owner =] 'source_owner'

Is the owner of the source object. source_owner is sysname, with a default of NULL. source_owner is automatically set to NULL for Microsoft® SQL Server™ version 6.5 Publishers.

[@sync_object_owner =] 'sync_object_owner'

Is the owner of the synchronization object. sync_object_owner is sysname, with a default of NULL. sync_object_owner is automatically set to NULL for Microsoft SQL Server 6.5 Publishers.

[@filter_owner =] 'filter_owner'

Is the owner of the filter. filter_owner is sysname, with a default of NULL. filter_owner is automatically set to NULL for Microsoft SQL Server 6.5 Publishers.

[@source_object =] 'source_object'

Is the table or stored procedure from which the article was created. 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 in SQL Server 7.0. source_table is provided for backward compatibility with SQL Server 6.x Publishers.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addarticle is used in snapshot, transactional, and merge replication.

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).

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

Permissions

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

See Also