Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_changemergepublication (Transact-SQL)

Updated: 12 December 2006

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

Topic link icon Transact-SQL Syntax Conventions


sp_changemergepublication [ @publication= ] 'publication'
    [ , [ @property= ] 'property' ]
    [ , [ @value= ] 'value' ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]
[ @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 Value Description

allow_anonymous

true

Anonymous subscriptions are allowed.

 

false

Anonymous subscriptions are not allowed.

allow_partition_realignment

true

Deletes are sent to the Subscriber to reflect the results of a partition change by removing data that is no longer part of the Subscriber's partition, which is the default behavior.

 

false

Data from an old partition is left on the Subscriber, where changes made to this data on the Publisher do not replicate to this Subscriber, but changes made on the Subscriber replicate to the Publisher. This is used to retain data in a subscription from an old partition when the data needs to be accessible for historical purposes.

allow_pull

true

Pull subscriptions are allowed for the given publication.

 

false

Pull subscriptions are not allowed for the given publication.

allow_push

true

Push subscriptions are allowed for the given publication.

 

false

Push subscriptions are not allowed for the given publication.

allow_subscriber_initiated_snapshot

true

Subscriber can initiate the snapshot process.

 

false

Subscriber cannot initiate the snapshot process.

allow_subscription_copy

true

Enables the ability to copy the subscription databases that subscribe to this publication.

 

false

Disables the ability to copy the subscription databases that subscribe to this publication.

allow_synctoalternate

true

Enables an alternate synchronization partner to synchronize with this Publisher.

 

false

Disables an alternate synchronization partner to synchronize with this Publisher.

allow_web_synchronization

true

Subscriptions can be synchronized over HTTPS.

 

false

Subscriptions cannot be synchronized over HTTPS.

alt_snapshot_folder

 

Specifies the location of the alternate folder for the snapshot.

automatic_reinitialization_policy

1

Changes are uploaded from the Subscriber before the subscription is reinitialized.

 

0

The subscription is reinitialized without first uploading changes.

centralized_conflicts

true

All conflict records are stored at the Publisher. Changing this property requires that existing Subscribers be reinitialized.

 

false

Conflict records are stored at the server that lost in the conflict resolution. Changing this property requires that existing Subscribers be reinitialized.

compress_snapshot

true

Snapshot in an alternate snapshot folder is compressed into the Microsoft CAB format. The snapshot in the default snapshot folder cannot be compressed. Changing this property requires a new snapshot.

 

false

Snapshot is not compressed, which is the default behavior for replication. Changing this property requires a new snapshot.

conflict_logging

publisher

Conflict records are stored at the Publisher.

 

subscriber

Conflict records are stored at the Subscriber that caused the conflict. Not supported for Microsoft SQL Server 2005 Compact Edition Subscribers.

 

both

Conflict records are stored at both the Publisher and Subscriber.

conflict_retention

 

int that specifies the retention period, in days, for which conflicts are retained. Setting conflict_retention to 0 means no conflict cleanup is needed.

description

 

Description of the publication.

dynamic_filters

true

Publication is filtered based on a dynamic clause.

 

false

Publication is not filtered dynamically.

enabled_for_internet

true

Publication is enabled for the Internet, and File Transfer Protocol (FTP) can be used to transfer the snapshot files to a subscriber. The synchronization files for the publication are put into the C:\Program Files\Microsoft SQL Server\MSSQL\Repldata\ftp directory.

 

false

Publication is not enabled for the Internet.

ftp_address

 

Is the network address of the FTP service for the Distributor. Specifies where publication snapshot files are stored.

ftp_login

 

Is the username used to connect to the FTP service.

ftp_password

 

Is the user password used to connect to the FTP service.

ftp_port

 

Is the port number of the FTP service for the Distributor. Specifies the TCP port number of the FTP site where the publication snapshot files are stored.

ftp_subdirectory

 

Specifies where the snapshot files are created if the publication supports propagating snapshots using FTP.

generation_leveling_threshold

int

Specifies the number of changes contained in a generation. A generation is a collection of changes that are delivered to a Publisher or Subscriber. For more information, see How Merge Replication Tracks and Enumerates Changes.

keep_partition_changes

true

Synchronization is optimized, and only Subscribers having rows in the changed partitions are affected. Changing this property requires a new snapshot.

 

false

Synchronization is not optimized, and the partitions sent to all Subscribers are verified when data changes in a partition. Changing this property requires a new snapshot.

max_concurrent_merge

 

int representing the maximum number of concurrent merge processes that can be run against a publication. A value of 0 for this property means that there is no limit to the number of concurrent merge processes running at any given time. If more than this number of merge processes are scheduled to run at the same time, then the excess jobs are put into a queue and wait until a currently-running merge process finishes.

max_concurrent_dynamic_snapshots

 

int representing the maximum number of concurrent snapshot sessions to generate a filtered data snapshot that can be running against a merge publication that uses parameterized row filters. If 0, there is no limit to the maximum number of concurrent filtered data snapshot sessions that can run simultaneously. If more than this number of snapshot processes are scheduled to run at the same time, then the excess jobs are put into a queue and wait until a currently-running merge process finishes.

post_snapshot_script

 

Specifies a pointer to an .sql file location. The Distribution Agent or Merge Agent runs the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization. Changing this property requires a new snapshot.

pre_snapshot_script

 

Specifies a pointer to an .sql file location. The Merge Agent runs the pre-snapshot script before any of the replicated object scripts when applying a snapshot at a Subscriber. Changing this property requires a new snapshot.

publication_compatibility_level

90RTM

Microsoft SQL Server 2005

 

80SP3

Microsoft SQL Server 2000 Service Pack 3

 

80SP1

SQL Server 2000 Service Pack 1

 

80RTM

SQL Server 2000

 

70SP3

Microsoft SQL Server 7.0 Service Pack 3

 

70SP2

SQL Server 7.0 Service Pack 2

 

70SP1

SQL Server 7.0 Service Pack 1

 

70RTM

SQL Server 7.0

publish_to_activedirectory

true

This parameter has been deprecated and is only supported for the backward compatibility of scripts. You can no longer add publication information to the Microsoft Active Directory.

 

false

Removes the publication information from Active Directory.

replicate_ddl

1

Data Definition Language (DDL) statements executed at the publisher are replicated.

 

0

DDL statements are not replicated.

retention

 

int representing the number of retention_period_unit units for which to save changes for the given publication. If the subscription is not synchronized within the retention period, and the pending changes it would have received have been removed by a clean-up operation at the Distributor, the subscription expires and must be reinitialized. The maximum allowable retention period is the number of days between December 31, 9999, and the current date.

The retention period for merge publications has a 24 hour grace period to accommodate Subscribers in different time zones. If, for example, you set a retention period of one day, the actual retention period is 48 hours.
ms186905.note(en-US,SQL.90).gifNote:

retention_period_unit

day

Retention period is specified in days.

 

week

Retention period is specified in weeks.

 

month

Retention period is specified in months.

 

year

Retention period is specified in years.

snapshot_in_defaultfolder

true

Snapshot files are stored in the default snapshot folder.

 

false

Snapshot files are stored in the alternate location specified by alt_snapshot_folder. This combination specifies that the snapshot files are stored in both the default and alternate locations.

snapshot_ready

true

Snapshot for the publication is available.

 

false

Snapshot for the publication is not available.

status

active

Publication is in an active state.

 

inactive

Publication is in an inactive state.

sync_mode

native or

bcp native

Native-mode bulk-copy program output of all tables is used for the initial snapshot.

 

character

or bcp character

Character-mode bulk-copy program output of all tables is used for the initial snapshot, which is required for all non-SQL Server Subscribers.

use_partition_groups

true

Publication uses precomputed partitions.

 

false

Publication does not use precomputed partitions.

validate_subscriber_info

 

Lists the functions that are being used to retrieve Subscriber information, and validates the dynamic filtering criteria being used for the Subscriber to verify that the information is partitioned consistently.

web_synchronization_url

 

Default value of the Internet URL used for Web synchronization.

NULL (default)

 

Returns the list of supported values for 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.

0 (success) or 1 (failure)

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.

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

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

Release History

12 December 2006

New content:
  • Added information about the generation_leveling_threshold value.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.