sp_changemergearticle

Transact-SQL Reference

Transact-SQL Reference

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.

See Also

sp_addmergearticle

sp_dropmergearticle

sp_helpmergearticle

System Stored Procedures