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.

[@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.

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.

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.

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.

[@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.

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.

[@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.

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

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