sp_changearticle
Changes the properties of an article in a transactional or snapshot publication. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_changearticle [ [@publication = ] 'publication' ]
[ , [ @article = ] 'article' ]
[ , [ @property = ] 'property' ]
[ , [ @value = ] 'value' ]
[ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription = ] force_reinit_subscription ]
Arguments
[@publication =] 'publication'
Is the name of the publication that contains the article. publication is sysname, with a default of NULL.
[@article =] 'article'
Is the name of the article whose property is to be changed. article is sysname, with a default of NULL.
[@property =] 'property'
Is an article property to change. property is nvarchar(20).
[@value =] 'value'
Is the new value of the article property. value is nvarchar(255).
This table describes the properties of articles and the values for those properties.
| Property | Values | Description |
|---|---|---|
| description | New descriptive entry for the publication job. | |
| sync_object | Name of the table or view used to produce a synchronization output file. The default is NULL. | |
| type | logbased (default) = 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 both manual filter and manual view. |
Article type. |
| ins_cmd | INSERT statement to execute; otherwise, it is constructed from the log. | |
| del_cmd | DELETE statement to execute; otherwise, it is constructed from the log. | |
| upd_cmd | UPDATE statement to execute; otherwise, it is constructed from the log. | |
| filter | New stored procedure to be used to filter the table (horizontal filtering). The default is NULL. | |
| dest_table | New destination table. | |
| dest_object | Provided for backward compatibility. Use dest_table. | |
| creation_script | Path and name of an article schema script used to create target tables. The default is NULL. | |
| pre_creation_cmd | Precreation command that can drop, delete, or truncate the destination table before synchronization is applied. | |
| none | Does not use a command. | |
| drop | Drops the destination table. | |
| delete | Deletes the destination table. | |
| truncate | Truncates the destination table. | |
| status | Specifies the new status of the property. | |
| include column names | Allows column names in the replicated INSERT statement. | |
| no column names | Allows no column names in the replicated INSERT statement. | |
| owner qualified | Allows owner-qualified table names. | |
| not owner qualified | Allows table names that are not owner-qualified. | |
| string literals | parameters | Specifies whether the logreader-generated commands use the standard string_literal command format or the new parameterized command format. | |
| schema_option | Specifies the bitmap of the schema generation option for the given article. schema_option is binary(8). 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. Also, 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. | |
| 0x00 | Disables scripting by InitialSync and uses the provided CreationScript. | |
| 0x01 | Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). | |
| 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. | |
| destination_owner | destination_owner | Name of the owner of the destination object. |
| NULL | NULL |
[@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 changes to the 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 changes to the 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 generated.
[@force_reinit_subscription = ] force_reinit_subscription
Acknowledges that the action taken by this stored procedure may require existing subscriptions to be reinitialized. force_reinit_subscription is a bit with a default of 0. 0 specifies that changes to the article will not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error will occur and no changes will be made. 1 specifies that changes to the article will cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_changearticle is used in snapshot replication and transactional replication.
Within an existing publication, you can use sp_changearticle to change an article without having to drop and re-create the entire publication.
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 type and replication types that are not valid pairs, and therefore, would 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_changearticle.