sp_addmergepublication
Creates a new merge publication. This stored procedure is executed at the Publisher on any database.
Syntax
sp_addmergepublication [ @publication = ] 'publication'
[ , [ @description = ] 'description'
[ , [ @retention = ] retention ]
[ , [ @sync_mode = ] 'sync_mode' ]
[ , [ @allow_push = ] 'allow_push' ]
[ , [ @allow_pull = ] 'allow_pull' ]
[ , [ @allow_anonymous = ] 'allow_anonymous' ]
[ , [ @enabled_for_internet = ] 'enabled_for_internet' ]
[ , [ @centralized_conflicts = ] 'centralized_conflicts' ]
[ , [ @dynamic_filters = ] 'dynamic_filters' ]
[ , [ @snapshot_in_defaultfolder = ] 'snapshot_in_default_folder' ]
[ , [ @alt_snapshot_folder = ] 'alternate_snapshot_folder' ]
[ , [ @pre_snapshot_script = ] 'pre_snapshot_script' ]
[ , [ @post_snapshot_script = ] 'post_snapshot_script' ]
[ , [ @compress_snapshot = ] 'compress_snapshot' ]
[ , [ @ftp_address = ] 'ftp_address' ]
[ , [ @ftp_port= ] ftp_port ]
[ , [ @ftp_subdirectory = ] 'ftp_subdirectory' ]
[ , [ @ftp_login = ] 'ftp_login' ]
[ , [ @ftp_password = ] 'ftp_password' ]
[ , [ @conflict_retention = ] conflict_retention ]
[ , [ @keep_partition_changes = ] 'keep_partition_changes' ]
[ , [ @allow_subscription_copy = ] 'allow_subscription_copy' ]
[ , [ @allow_synctoalternate = ] 'allow_synctoalternate' ]
[ , [ @validate_subscriber_info = ] 'validate_subscriber_info' ]
[ , [ @add_to_active_directory = ] 'add_to_active_directory' ]
[ , [ @max_concurrent_merge = ] maximum_concurrent_merge ]
[ , [ @max_concurrent_dynamic_snapshots =] max_concurrent_dynamic_snapshots ]
Arguments
[@publication = ] 'publication'
Is the name of the merge publication to create. publication is sysname, with no default, and must not be the keyword ALL. The name of the publication must be unique within the database.
[@description = ] 'description'
Is the publication description. description is nvarchar(255), with a default of NULL.
[@retention = ] retention
Is the number of days for which to save changes for the given publication. retention is int, with a default of 14 days. If the subscription does not merge within the retention period, the subscription expires and is removed.
[@sync_mode = ] 'sync_mode'
Is the mode of the initial synchronization of subscribers to the publication. sync_mode is nvarchar(10), with a default of native. 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.
[@allow_push = ] 'allow_push'
Specifies if push subscriptions can be created for the given publication. allow_push is nvarchar(5), with a default of TRUE, which allows push subscriptions on the publication.
[@allow_pull = ] 'allow_pull'
Specifies if pull subscriptions can be created for the given publication. allow_pull is nvarchar(5), with a default of TRUE, which allows pull subscriptions on the publication.
[@allow_anonymous = ] 'allow_anonymous'
Specifies if anonymous subscriptions can be created for the given publication. allow_anonymous is nvarchar(5), with a default of FALSE, which does not allow anonymous subscriptions on the publication.
[@enabled_for_internet = ] 'enabled_for_internet'
Specifies if the publication is enabled for the Internet, and determines if FTP can be used to transfer the snapshot files to a subscriber. enabled_for_internet is nvarchar(5), with a default of FALSE. If true, the synchronization files for the publication are put into the C:\Program Files\Microsoft SQL Server\MSSQL\Repldata\Ftp directory. The user must create the Ftp directory. If false, the publication is not enabled for Internet access.
[@centralized_conflicts = ] 'centralized_conflicts'
Specifies if conflict records are stored on the Publisher. centralized_conflicts is nvarchar(5), with a default of TRUE. If true, all conflict records are stored at the Publisher. If false, conflict records are stored at both the publisher and at the subscriber that caused the conflict.
[@dynamic_filters = ] 'dynamic_filters'
Enables the merge publication to allow dynamic filters. dynamic_filter is nvarchar(5), with a default of FALSE.
[@snapshot_in_defaultfolder = ] 'snapshot_in_default_folder'
Specifies if the snapshot files are stored in the default folder. snapshot_in_default_folder is nvarchar(5), with a default of TRUE. If true, snapshot files can be found in the default folder. If false, snapshot files will be stored in the alternate location specified by alternate_snapshot_folder. Alternate locations can be on another server, on a network drive, or on a removable media (such as CD-ROM or removable disks). You can also save the snapshot files to a File Transfer Protocol (FTP) site, for retrieval by the Subscriber at a later time. Note that this parameter can be true and still have a location 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 = ] 'alternate_snapshot_folder'
Specifies the location of the alternate folder for the snapshot. alternate_snapshot_folder is nvarchar(255), with a default of NULL.
[@pre_snapshot_script = ] '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 the snapshot at a Subscriber.
[@post_snapshot_script = ] 'post_snapshot_script'
Specifies a pointer to an .sql file location. post_snapshot_script is nvarchar(255), with a default of NULL. 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 = ] 'compress_snapshot'
Specifies that the snapshot written to the @alt_snapshot_folder location is to be compressed into the Microsoft® CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed; true specifies that the snapshot is to be compressed. The snapshot in the default folder cannot be compressed.
[@ftp_address = ] 'ftp_address'
Is the network address of the FTP service for the Distributor. ftp_address is sysname, with a default of NULL. Specifies where publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have a different ftp_address. The publication must support propagating snapshots using FTP. For more information, see Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP.
[@ftp_port= ] ftp_port
Is the port number of the FTP service for the Distributor. ftp_port is int, with a default of 21. Specifies where the publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have its own ftp_port.
[@ftp_subdirectory = ] 'ftp_subdirectory'
Specifies where the snapshot files will be available for the Merge Agent of the subscriber to pick up if the publication supports propagating snapshots using FTP. ftp_subdirectory is nvarchar(255), with a default of NULL. Since this property is stored for each publication, each publication can have its own ftp_subdirctory or choose to have no subdirectory, indicated with a NULL value.
[@ftp_login = ] 'ftp_login'
Is the username used to connect to the FTP service. ftp_login is sysname, with a default of 'anonymous'.
[@ftp_password = ] 'ftp_password'
Is the user password used to connect to the FTP service. ftp_password is sysname, with a default of NULL.
[@conflict_retention = ] conflict_retention
Specifies the retention period, in days, for which conflicts are retained. conflict_retention is int, with a default of 14 days before the conflict row is purged from the conflict table.
[@keep_partition_changes = ] 'keep_partition_changes'
Specifies whether synchronization optimization should occur. keep_partition_changes is nvarchar(5), with a default of FALSE. false means that synchronization is not optimized, and the partitions sent to all Subscribers will be verified when data changes in a partition. true means that synchronization is optimized, and only Subscribers having rows in the changed partition(s) are affected. For more information, see Optimizing Synchronization.
[@allow_subscription_copy = ] 'allow_subscription_copy'
Enables or disables the ability to copy the subscription databases that subscribe to this publication. allow_subscription_copy is nvarchar(5), with a default of FALSE.
[@allow_synctoalternate = ] 'allow_synctoalternate'
Enables an alternate synchronization partner to synchronize with this Publisher. allow_synctoalternate is nvarchar(5), with a default of FALSE.
[@validate_subscriber_info = ] '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 with each merge. validate_subscriber_info is nvarchar(500), with a default of NULL. For example, if SUSER_SNAME() is used in the dynamic filter, the parameter should be @validate_subscriber_info=N'SUSER_SNAME()'. For more information, see Validate Subscriber Information.
[@add_to_active_directory = ] 'add_to_active_directory'
Specifies if the publication information is published to the Microsoft Active Directory™. add_to_active_directory is nvarchar(5), with a default of FALSE. This feature is available only for servers running on the Windows® 2000 operating system. A value of true will add the publication information to the Microsoft Active Directory.
[@max_concurrent_merge = ] maximum_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 =] 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.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_addmergepublication is used in merge replication.
To list publication objects to the Active Directory using the @add_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_addmergepublication.
See Also
Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP