sp_changemergearticle
Changes the properties of a merge article. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_changemergearticle [ @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 in which the article exists. publication is sysname, with no default.
[@article =] 'article'
Is the name of the article to change. article is sysname, with no default.
[@property =] 'property'
Is the property to change for the given article and publication. property is nvarchar(30), and can be one of the values listed in the table.
[@value =] 'value'
Is the new value for the specified property. value is nvarchar(1000), and can be one of the values listed in the table.
This table describes the properties of articles and the values for those properties.
| Property | Values | Description |
|---|---|---|
| description | Descriptive entry for the article. | |
| pre_creation_command | none: If the table already exists at the Subscriber, no action is taken.
drop:Issues a delete based on the WHERE clause in the subset filter. delete:Drops the table before re-creating it. truncate: Same as delete, but deletes pages instead of rows. Does not accept a WHERE clause. |
Specifies what the system is to do if the tables exists at the subscriber when applying the snapshot. |
| creation_script | Path and name of an optional article schema script used to create target table. | |
| column_tracking | true or false | Setting for column level tracking. true turns on column level tracking. false turns off column level tracking and leaves conflict detection at the row level. If the table is already published inother merge publications, the column tracking must be the same as the value being used by existing articles based on this table. This parameter is specific to table articles only. |
| article_resolver | Custom resolver for the article. | |
| resolver_info | Name of the stored procedure used as a custom resolver. | |
| status | active or unsynced, or | Status of the article. If active, the intial processing script to publish the table is run. If unsynced, the intial processing script to publish the table is run at the next time the Snapshot Agent runs. |
| subset_filterclause | WHERE clause specifying the horizontal filtering. | |
| schema_option | 0x00: Disables scripting by the Snapshot Agent and uses the script provided in creation_script.
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. |
Bitmap of the schema generation option for the given article. 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. |
| destination_owner | Name of the owner of the object in the subscription database, if not 'dbo'. | |
| destination_object | New name of the destination object, '', or NULL. If NULL or '', the value will be reset to be equivalent to the current value in the source_object property for the article.
Valid for merge stored procedures, views, and UDF schema articles only. Modifying the destination_object of a merge table article will result in an error. |
|
| pub_identity_range | Range size at the Publisher if the article has auto_identity_range set to true. Applies to a table article only. | |
| identity_range | The range size at the Subscriber if the article has auto_identity_range set to true. Applies to a table article only. | |
| threshold | Percentage value that controls when the merge agent assigns a new identity range. When the percentage of values specified in threshold is used, the Merge Agent creates a new identity range. Used when the auto_identity_range is set to true. Applies to a table article only. | |
| verify_resolver_signature | 0 or 1 | A bit value that specifies if a digital signature is verified before using a resolver in merge replication. A value of 0 specifies that the signature will not be verified. A value of 1 specifies that the signature will be verified to see if it is from a trusted source. |
| allow_interactive_resolver | true or false | A bit value that enables or disables the use of the Interactive Resolver on an article. A value of true enables the use of the Interactive Resolver on the article; a value of false disables it. |
| check_permissions | A value of 0x00 specifies that permissions will not be checked.
A value of 0x10 specifies that permissions will be checked at the Publisher before INSERTs, which have been made at a Subscriber, can be uploaded. A value of 0x20 specifies that permissions will be checked at the Publisher before UPDATEs, which have been made at a Subscriber, can be uploaded. A value of 0x40 specifies that permissions will be checked at the Publisher before DELETEs, which have been made at a Subscriber, can be uploaded. |
Bitmap of the table-level permissions that will be verified when the Merge Agent applies changes to the Publisher. If the Publisher login/user account used by the merge process does not have the correct table permissions, the invalid changes will be logged as conflicts. check_permissions is int. |
| NULL (default) |
[@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 merge 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 means that changes to the merge 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 merge 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 means that changes to the merge 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_changemergearticle is used in merge replication.
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 |
|---|---|
| Merge | |
| logbased | |
| logbased manualfilter | |
| logbased manualview | |
| indexed view logbased | |
| indexed view logbased manualfilter | |
| indexed view logbased manualview | |
| indexed view logbase manualboth | |
| proc exec | |
| serialized proc exec | |
| proc schema only | 0x01 |
| view schema only | 0x01 |
| func schema only | 0x01 |
| indexed view schema only | 0x01 |
| table | 0xCFF1 |
Valid Schema Option Table
| Article Type | Replication Type |
|---|---|
| Merge | |
| logbased | |
| logbased manualfilter | |
| logbased manualview | |
| indexed view logbased | |
| indexed view logbased manualfilter | |
| indexed view logbased manualview | |
| indexed view logbase manualboth | |
| proc exec | 0x01 and 0x2000 |
| serialized proc exec | 0x01 and 0x2000 |
| proc schema only | 0x01 and 0x2000 |
| view schema only | 0x01, 0x0100, and 0x2000 |
| func schema only | 0x01 and 0x2000 |
| indexed view schema only | 0x01, 0x10, 0x040, 0x0100, and 0x2000 |
| table | All options but 0x02 and 0x8000 |
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_changemergearticle.