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.