sp_changemergepublication (Transact-SQL)

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

Transact-SQL Syntax Conventions

Syntax

sp_changemergepublication [ @publication= ] 'publication'
    [ , [ @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. publication is sysname, with no default.

• [ @property=] 'property'
  Is the property to change for the given publication. property is sysname, and can be one of the values listed in the table.

• [ @value=] 'value'
  Is the new value for the specified property. value is nvarchar(255), and can be one of the values listed in the table.

  This table describes the properties of the publication that can be changed and restrictions on 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 changing the publication does 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 specifies that changing the publication 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 changing the publication does not cause a need for subscriptions 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 specifies that changing the publication causes 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_changemergepublication is used in merge replication.

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.

• alt_snapshot_folder
• compress_snapshot
• dynamic_filters
• ftp_address
• ftp_login
• ftp_password
• ftp_port
• ftp_subdirectory
• post_snapshot_script
• publication_compatibility_level (to 80SP3 only)
• pre_snapshot_script
• snapshot_in_defaultfolder
• sync_mode
• use_partition_groups

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

• dynamic_filters
• validate_subscriber_info

To list publication objects to the Active Directory using the publish_to_active_directory, the SQL Server object must already be created in the Active Directory.

Permissions

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

Example

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

-- Disable DDL replication for the publication.
USE [AdventureWorks]
EXEC sp_changemergepublication 
  @publication = @publication, 
  @property = N'replicate_ddl', 
  @value = 0,
  @force_invalidate_snapshot = 0, 
  @force_reinit_subscription = 0;
GO

See Also

Reference

sp_addmergepublication (Transact-SQL)
sp_dropmergepublication (Transact-SQL)
sp_helpmergepublication (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

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

Help and Information

Getting SQL Server 2005 Assistance

Change History