Transact-SQL Reference

sp_changemergepublication

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

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 Values Description
description   Description of the publication.
status active or unsynced Status of the publication.
retention   Number of days for which to save changes for the given publication.
sync_mode native or character Mode of the intial synchronization of subscribers to the publication. If native, native-mode bulk copy program output of all tables is produced. If character, character-mode bulk copy program output of all tables is produced. Non-SQL Server subscribers require the use of character mode.
Allow_push true or false Push subscriptions are allowed for the given publication.
Allow_pull true or false Pull subscriptions are allowed for the given publication.
allow_anonymous true or false Anonymous subscriptions are allowed for the given publication.
enabled_for_internet true or false Publication is enabled for the Internet, and specifies if FTP can be use to transfer the snapshot files to a subscriber. If true, the synchronization files for the publication are put into the C:\Program Files\Microsoft SQL Server\MSSQL\Repldata\ftp directory.
centalized_conflicts true or false Conflict records are stored on the given Publisher if true. If false, conflict records are stored at the server where the conflict was detected, which could be at the Publisher or the Subscriber.
snapshot_ready true or false Snapshot for the publication is available.
snapshot_in_defaultfolder true or false Specifies if the snapshot is stored in the default folder. If true, snapshot files can be found in the default folder. If false, snapshot files will be stored in the alternate location specified by the alt_snapshot_folder. Note that this parameter can be true and have a location specified in the alt_snapshot_folder parameter. This combination specifies that the snapshot files will be stored in both the default and alternate locations.
alt_snapshot_folder   Specifies the location of the alternate folder for the snapshot.
pre_snapshot_script   Specifies a pointer to an .sql file location. pre_snapshot_script is nvarchar(255), with a default of NULL. The Merge Agent will run the pre-snapshot script before any of the replicated object scripts when applying a snapshot at a Subscriber.
post_snapshot_script   Specifies a pointer to an .sql file location. The Distribution Agent or Merge Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization.
compress_snapshot true or false Specifies that the snapshot that is written to the @alt_snapshot_folder location is to be compressed into a Microsoft® CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed, while true specifies that the snapshot is to be compressed. The snapshot in the default folder cannot be compressed.
ftp_address   Is the network address of the FTP service for the Distributor. Specifies where publication snapshot files are stored.
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.
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.
conflict_retention   Specifies the retention period, in days, for which conflicts are retained.
allow_subscription_copy true or false Enables or disables the ability to copy the subscription databases that subscribe to this publication.
allow_synctoalternate true or false Enables an alternate synchronization partner to synchronize with this Publisher.
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. For example, if SUSER_SNAME() is used in the dynamic filter, this parameter should be specified as @validate_subscriber_info=N'SUSER_SNAME()'. For more information, see Validate Subscriber Information.
publish_to_activedirectory   Specifies whether the publication information is published to the Microsoft Active Directory™. This feature is available only for servers running the Microsoft Windows® 2000 operating system. A value of true will add the publication information to the Microsoft Active Directory.
dynamic_filters true or false Specifies whether the publication is filtered on a dynamic clause.
max_concurrent_merge   The maximum number of concurrent merge processes. 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. This property sets a limit on the number of concurrent merge processes that can be run against a merge publication at one time. If there are more snapshot processes scheduled at the same time than the value allows to run, then the excess jobs will be put into a queue and wait until a currently-running merge process finishes.
max_concurrent_dynamic_snapshots   The maximum number of concurrent dynamic snapshot sessions that can be running against the merge publication. If 0, there is no limit to the maximum number of concurrent dynamic snapshot sessions that can run simultaneously against the publication at any given time. This property sets a limit on the number of concurrent snapshot processes that can be run against a merge publication at one time. If there are more snapshot processes scheduled at the same time than the value allows to run, then the excess jobs will be put into a queue and wait until a currently-running merge process finishes.
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 changing the publication 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 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.

[@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 will 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 will occur and no changes will be made. 1 specifies that changing the publication 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_changemergepublication is used in merge replication.

To list publication objects to the Active Directory using the @publish_to_active_directory parameter, the SQL Server object must already be created in the Active Directory. For more information, see Active Directory Services.

Permissions

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

See Also

sp_addmergepublication

sp_dropmergepublication

sp_helpmergepublication

System Stored Procedures