sp_addarticle

Transact-SQL Reference

Transact-SQL Reference

sp_addarticle

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

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 ]

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'

Is the name of the underlying table represented by the article or stored procedure. source_table is nvarchar(386), which must be on the local SQL Server computer, 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'

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. 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 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 these values.

Value Description
NONE No action is taken.
CALL sp_MSins_article

-or-

CALL custom_stored_procedure_name

(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. custom_stored_procedure is the name of a user-created stored procedure. sp_Msins_article contains the name of the article in place of the _article part of the parameter. For example, for the Categories table, the parameter would be CALL sp_Msins_Categories.
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 command type used when replicating deletes for this article. del_cmd is nvarchar(255), and can be one of these values.

Value Description
NONE No action is taken.
CALL sp_MSdel_article -or-

CALL custom_stored_procedure_name

(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. custom_stored_procedure is the name of a user-created stored procedure. sp_Msins_article contains the name of the article in place of the _article part of the parameter. For example, for the Categories table, the parameter would be CALL sp_Msins_Categories.
XCALL sp_MSdel_article 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.
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

Note  The CALL, MCALL, 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 MCALL syntax passes values only for affected columns. The XCALL syntax passes values for all columns, whether changed or not, plus the "before" value of the column. For more information, see Using Custom Stored Procedures in 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 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. 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_article contains the name of the article in place of the _article part of the parameter. For example, for the Categories table, the parameter would be CALL sp_Msins_Categories.
XCALL sp_MSupd_article 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.
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, 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 MCALL 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 Using Custom Stored Procedures in Articles.

[@creation_script = ] 'creation_script'

Is the path and name of an article schema script used to create target table. 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'

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 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 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 Generate Filters Automatically.

[@schema_option = ] schema_option

Is a bitmask of the schema generation option for the given article. It specifies the automatic creation of the stored procedure in the destination database for all CALL/MCALL/XCALL. schema_option is binary(8), and can be a combination of these values. If this value is NULL, the system will auto-generate a valid schema option for the article. The 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 Description
0x00 Disables scripting by the Snapshot Agent and uses creation_script.
0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
0x02 Generates custom stored procedures for the article, if defined.
0x10 Generates a corresponding clustered index.
0x20 Converts user-defined data types to base data types.
0x40 Generates corresponding nonclustered index(es).
0x80 Includes declared referential integrity on the primary keys.
0x100 Replicates user triggers on a table article, if defined.
0x200 Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
0x400 Replicates check constraints.
0x800 Replicates defaults.
0x1000 Replicates column-level collation.
0x2000 Replicates extended properties associated with the published article source object.
0x4000 Replicates unique keys if defined on a table article.
0x8000 Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements.
NULL  

Not all @schema_option values are valid for every type of replication and article type. The Valid Schema Option table given in the Remarks shows the valid schema options that can be chosen based upon the combination of the article type and the replication type.

[@destination_owner = ] 'destination_owner'

Is the name of the owner of the destination object. destination_owner is sysname, with a default of NULL. If ODBC Subscribers can subscribe to the publication, 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 Includes the column name in INSERT statements.
16 (default) Uses parameterized statements.
24 Includes the column name in INSERT statements and uses parameterized statements.

[@source_owner = ] 'source_owner'

Is the owner of the source object. source_owner is sysname, with a default of NULL.

[@sync_object_owner = ] 'sync_object_owner'

Is the owner of the synchronization object. 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 table or stored procedure 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. source_table is provided for backward compatibility with SQL Server 6.x Publishers.

[@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), with a default of FALSE. true enables automatic identity range handling; false disables it. Note that identity range management only pertains to snapshot or transactional publications that allow immediate updating or queued updating. For more information, see Managing Identity Values.

[@pub_identity_range = ] pub_identity_range

Controls the range size at the Publisher if the article has auto_identity_range set to true. pub_identity_range is bigint, with a default of NULL.

[@identity_range = ] identity_range

Controls the range size at the Subscriber if the article has auto_identity_range set to true. identity_range is bigint, with a default of NULL. Used when auto_identity_range is set to true.

[@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 auto_identity_range is set to true.

[@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 will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be made. 1 specifies that adding an article may 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 to be generated.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addarticle is used in snapshot replication or transactional replication.

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 immediate-updating subscriptions and the published table does not have a uniqueidentifier column, sp_addarticle adds a uniqueidentifier column to the table automatically.

The table describes the default @schema_option value that will be chosen for the stored procedure if a NULL value is passed in by the user. The default value is based upon the replication type shown across the top, and the article type shown down the first column.  Empty cells are article and replication type pairs that are not valid combinations, and therefore, have no default.

Article Type Replication Type
  Transactional Snapshot
logbased 0xF3 0x71
logbased manualfilter 0xF3 0x71
logbased manualview 0xF3 0x71
indexed view logbased 0xF3 0x71
indexed view logbased manualfilter 0xF3 0x71
indexed view logbased manualview 0xF3 0x71
indexed view logbase manualboth 0xF3 0x71
proc exec 0x01 0x01
serialized proc exec 0x01 0x01
proc schema only 0x01 0x01
view schema only 0x01 0x01
func schema only 0x01 0x01
indexed view schema only 0x01 0x01
table    

Note  If a publication is enabled for queued updating, the @schema_option values of 0x8000 and 0x0080 will be added to the default value shown in the table.

Valid Schema Option Table

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 and 0x2000 0x01 and 0x2000
serialized proc exec 0x01 and 0x2000 0x01 and 0x2000
proc schema only 0x01 and 0x2000 0x01 and 0x2000
view schema only 0x01, 0x0100, and 0x2000 0x01, 0x0100, and 0x2000
func schema only 0x01 and 0x2000 0x01 and 0x2000
indexed view schema only 0x01, 0x10, 0x040, 0x0100, and 0x2000 0x01, 0x10, 0x040, 0x0100, and 0x2000
table    

Note  For queued updating publications, the @schema_option values of 0x8000 and 0x80 must be enabled.

Permissions

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

See Also

Enhancing Transactional Replication Performance

sp_addpublication

sp_articlecolumn

sp_articlefilter

sp_articleview

sp_changearticle

sp_changepublication

sp_droparticle

sp_droppublication

sp_enumfullsubscribers

sp_helparticle

sp_helparticlecolumns

sp_helppublication

System Stored Procedures