sp_changemergearticle (Transact-SQL)

Changes the properties of a merge article. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL Syntax Conventions

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

• [ @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 do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are 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.

  See the Remarks section for the properties that, when changed, require the generation of a new snapshot.

• [ @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 do not cause the subscription to be reinitialized. If the stored procedure detects that the change would require existing subscriptions to be reinitialized, an error occurs and no changes are made.

  1 means that changes to the merge article cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

  See the Remarks section for the properties that, when changed, require that all existing subscriptions be reinitialized.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changemergearticle is used in merge replication.

Because sp_changemergearticle is used to change article properties that were initially specified by using sp_addmergearticle, refer to sp_addmergearticle for additional information about these properties.

Changing the following properties requires that a new snapshot be generated, and you must specify a value of 1 for the force_invalidate_snapshot parameter:

• check_permissions
• column_tracking
• destination_owner
• pre_creation_cmd
• schema_options
• subset_filterclause

Changing the following properties requires that existing subscriptions be reinitialized, and you must specify a value of 1 for the force_reinit_subscription parameter:

• check_permissions
• column_tracking
• destination_owner
• subscriber_upload_options
• subset_filterclause

When specifying a value of 3 for partition_options, metadata is cleaned up whenever the Merge Agent runs and the partitioned snapshot expires more quickly. When using this option, you should consider enabling subscriber requested partitioned snapshot. For more information, see Snapshots for Merge Publications with Parameterized Filters.

When setting the column_tracking property, if the table is already published in other 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.

If multiple publications publish articles based on the same underlying table, changing the delete_tracking property or the compensate_for_errors property for one article causes the same change to be made to the other articles that are based on the same table.

If the Publisher login/user account used by the merge process does not have the correct table permissions, the invalid changes are logged as conflicts.

When changing the value of schema_option, the system does not perform a bitwise update. This means that when you set schema_option using sp_changemergearticle, existing bit settings may be turned off. To retain the existing settings, you should perform & (Bitwise AND) between the value that you are setting and the current value of schema_option, which can be determined by executing sp_helpmergearticle.

Valid Schema Option Table

The following table describes the allowed schema_optionvalues, depending on article type.

Article type	Schema option values
func schema only	0x01 and 0x2000
indexed view schema only	0x01, 0x040, 0x0100, 0x2000, 0x40000, 0x1000000, and 0x200000
proc schema only	0x01 and 0x2000
table	All options.
view schema only	0x01, 0x040, 0x0100, 0x2000, 0x40000, 0x1000000, and 0x200000

Permissions

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

Example

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @article = N'SalesOrderHeader';

-- Enable column-level conflict tracking.
-- Changing this property requires that existing subscriptions
-- be reinitialized and that a new snapshot be generated.
USE [AdventureWorks]
EXEC sp_changemergearticle 
  @publication = @publication,
  @article = @article, 
  @property = N'column_tracking', 
  @value = N'true',
  @force_invalidate_snapshot = 1,
  @force_reinit_subscription = 1;
GO

See Also

Reference

sp_addmergearticle (Transact-SQL)
sp_dropmergearticle (Transact-SQL)
sp_helpmergearticle (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

How to: View and Modify Article Properties (Replication Transact-SQL Programming)
Changing Publication and Article Properties

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Updated the description of the 0x20000000 value of the schema_option property.
5 December 2005	New content: Documented additional restrictions when setting the 0x20 option for the schema_option parameter. Changed content: Updated information on the default schema option value for table articles.