sp_changemergepublication (Transact-SQL)

Стаття
11/24/2023

Applies to: SQL Server

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 = ] N'publication'
    [ , [ @property = ] N'property' ]
    [ , [ @value = ] N'value' ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication. @publication is sysname, with no default.

[ @property = ] N'property'

The property to change for the given publication. @property is sysname, and can be one of the values listed in the table that follows.

[ @value = ] N'value'

The new value for the specified property. @value is nvarchar(255), with a default of NULL.

, and can be one of the values listed in the table that follows.

This table describes the properties of the publication that can be changed, and describes restrictions on the values for those properties.

Property	Value	Description
`allow_anonymous`	`true`	Anonymous subscriptions are allowed.
	`false`	Anonymous subscriptions aren't 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. This is the default behavior.
	`false`	Data from an old partition is left on the Subscriber, where changes made to this data on the Publisher don't replicate to this Subscriber. Instead, changes that are made on the Subscriber replicate to the Publisher. This is used to retain data in a subscription from an old partition when the data has to be accessible for historical purposes.
`allow_pull`	`true`	Pull subscriptions are allowed for the given publication.
	`false`	Pull subscriptions aren't allowed for the given publication.
`allow_push`	`true`	Push subscriptions are allowed for the given publication.
	`false`	Push subscriptions aren't allowed for the given publication.
`allow_subscriber_initiated_snapshot`	`true`	Subscriber can initiate the snapshot process.
	`false`	Subscriber can't initiate the snapshot process.
`allow_subscription_copy`	`true`	You can copy the subscription databases that subscribe to this publication.
	`false`	You can't copy the subscription databases that subscribe to this publication.
`allow_synctoalternate`	`true`	Allows an alternative synchronization partner to synchronize with this Publisher.
	`false`	Doesn't allow an alternative synchronization partner to synchronize with this Publisher.
`allow_web_synchronization`	`true`	Subscriptions can be synchronized over HTTPS.
	`false`	Subscriptions can't be synchronized over HTTPS.
`alt_snapshot_folder`		Specifies the location of the alternative 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. If you change this property, existing Subscribers must be reinitialized.
	`false`	Conflict records are stored at the server that lost in the conflict resolution. If you change this property, existing Subscribers must be reinitialized.
`compress_snapshot`	`true`	Snapshot in an alternative snapshot folder is compressed into the CAB format. The snapshot in the default snapshot folder can't be compressed. Changing this property requires a new snapshot.
	`false`	By default, the snapshot isn't compressed. 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 SQL Server Compact Subscribers.
	`both`	Conflict records are stored at both the Publisher and Subscriber.
`conflict_retention`		An 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 isn't filtered dynamically.
`enabled_for_internet`	`true`	Publication is enabled for the Internet. 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 isn't enabled for the Internet.
`ftp_address`		The network address of the FTP service for the Distributor. Specifies where publication snapshot files are stored.
`ftp_login`		The user name that is used to connect to the FTP service.
`ftp_password`		The user password that is used to connect to the FTP service.
`ftp_port`		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 by using FTP.
`generation_leveling_threshold`	int	Specifies the number of changes that are contained in a generation. A generation is a collection of changes that are delivered to a Publisher or Subscriber.
`keep_partition_changes`	`true`	Synchronization is optimized, and only Subscribers that have rows in the changed partitions are affected. Changing this property requires a new snapshot.
	`false`	Synchronization isn't optimized, and the partitions that are sent to Subscribers are verified when data changes in a partition. Changing this property requires a new snapshot.
`max_concurrent_merge`		An int that represents the maximum number of concurrent merge processes that can be run against a publication. If 0, there's no limit. If more than this number of merge processes are scheduled to run at the same time, the excess jobs are put into a queue until a currentlmerge process finishes.
`max_concurrent_dynamic_snapshots`		An int that represents the maximum number of snapshot sessions to generate a filtered data snapshot that can concurrently run against a merge publication that uses parameterized row filters. If `0`, there's no limit. If more than this number of snapshot processes are scheduled to run at the same time, the excess jobs are put into a queue until a current 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`	`100RTM`	SQL Server 2008 (10.0.x)
	`90RTM`	SQL Server 2005 (9.x)
`publish_to_activedirectory`	`true`	This parameter is deprecated and is maintained for backward compatibility of scripts. You can no longer add publication information to Active Directory.
	`false`	Removes the publication information from Active Directory.
`replicate_ddl`	`1`	Data Definition Language (DDL) statements that are executed at the Publisher are replicated.
	`0`	DDL statements aren't replicated.
`retention`		An int that represents the number of `retention_period_unit` units for which to save changes for the given publication. If the subscription isn't synchronized within the retention period, and the pending changes it received were 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. Note: The retention period for merge publications has a 24 hour grace period to accommodate Subscribers in different time zones.
`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 alternative location that is specified by `alt_snapshot_folder`. This combination specifies that the snapshot files are stored in both the default and alternative locations.
`snapshot_ready`	`true`	Snapshot for the publication is available.
	`false`	Snapshot for the publication isn't 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` Note: After using partition groups, if you to revert to using `setupbelongs`, and set `use_partition_groups=false` in `changemergearticle`, this might not be correctly reflected after a snapshot is taken. The triggers generated by snapshot are compliant with partition groups. The workaround to this scenario is to set the status to Inactive, modify the `use_partition_groups`, and then set status to Active.	`true`	Publication uses precomputed partitions.
	`false`	Publication doesn't use precomputed partitions.
`validate_subscriber_info`		Lists the functions that are being used to retrieve Subscriber information. Then, validates the dynamic filtering criteria 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 might invalidate an existing snapshot. @force_invalidate_snapshot is bit, with a default of 0.

0 specifies that changing the publication doesn't invalidate the snapshot. 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 might invalidate the snapshot. If there are existing subscriptions that require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and for a new snapshot to be generated.

See the Remarks section for the properties that, when changed, require a new snapshot to be generated.

[ @force_reinit_subscription = ] force_reinit_subscription

Acknowledges that the action taken by this stored procedure might require existing subscriptions to be reinitialized. @force_reinit_subscription is bit, with a default of 0.

0 specifies that changing the publication doesn't require that subscriptions be reinitialized. If the stored procedure detects that the change requires existing subscriptions to be reinitialized, an error occurs and no changes are made.
1 means that changes to the publication reinitialize existing subscriptions, 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 is generated. 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. You must specify a value of 1 for the @force_reinit_subscription parameter.

dynamic_filters
validate_subscriber_info

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

Examples

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

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

Permissions

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

Поділитися через

sp_changemergepublication (Transact-SQL)

Syntax

Arguments

[ @publication = ] N'publication'

[ @property = ] N'property'

[ @value = ] N'value'

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

[ @force_reinit_subscription = ] force_reinit_subscription

Return code values

Remarks

Examples

Permissions

Зворотний зв’язок

Додаткові ресурси

Поділитися через

sp_changemergepublication (Transact-SQL)

Syntax

Arguments

[ @publication = ] N'publication'

[ @property = ] N'property'

[ @value = ] N'value'

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

[ @force_reinit_subscription = ] force_reinit_subscription

Return code values

Remarks

Examples

Permissions

Related content

Зворотний зв’язок

Додаткові ресурси