Collapse the table of content
Expand the table of content


SQL Server 2000

  New Information - SQL Server 2000 SP3.

Returns information about a merge publication. This stored procedure is executed at the Publisher on the publication database.


sp_helpmergepublication [ [ @publication = ] 'publication']
    [ , [ @found = ] 'found'OUTPUT]
    [ , [ @publication_id = ] 'publication_id' OUTPUT]
    [ , [ @reserved = ] 'reserved' ]


[@publication = ] 'publication'

Is the name of the publication. publication is sysname, with a default of %, which returns information about all merge publications in the current database.

[@found = ] 'found' OUTPUT

Is a flag to indicate returning rows. found is int and an OUTPUT parameter, with a default of NULL. 1 indicates the publication is found. 0 indicates the publication is not found.

[@publication_id = ] 'publication_id' OUTPUT

Is the publication identification number. publication_id is uniqueidentifier and an OUTPUT parameter, with a default of NULL.

[@reserved = ] 'reserved'

Is reserved for future use. reserved is nvarchar(20), with a default of NULL.

Result Sets
Column name Data type Description
id int Sequential order of the publication in the list.
name sysname Name of the publication.
description nvarchar(255) Description of the publication.
status tinyint When publication data will be available.
retention int Amount of change, in days, to save for the given publication.
sync_mode tinyint Synchronization mode of this publication:

0 = Native bulk copy program (bcp utility)
1 = Character bulk copy

allow_push int Whether push subscriptions can be created for the given publication. 0 means that a push subscription is not allowed.
allow_pull int Whether pull subscriptions can be created for the given publication. 0 means that a pull subscription is not allowed.
allow_anonymous int Whether anonymous subscriptions can be created for the given publication. 0 means that an anonymous subscription is not allowed.
centralized_conflicts int Whether conflict records are stored on the given Publisher:

0 = conflict records are stored at both the publisher and at the subscriber that caused the conflict.
1 = all conflict records are stored at the Publisher.

priority float(8) Priority of the loop-back subscription.
snapshot_ready tinyint Whether the snapshot of this publication is ready:

0 = Snapshot is ready for use.
1 = Snapshot is not ready for use.

publication_type int Type of publication:

0 = Snapshot.
1 = Transactional.
2 = Merge.

pubid uniqueidentifier Unique identifier of this publication.
snapshot_jobid binary(16) Job ID of the Snapshot Agent.
enabled_for_internet int Whether the publication is enabled for the Internet. If 1, 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 0, the publication is not enabled for Internet access.
dynamic_filter int Whether a dynamic filter is used. 0 means a dynamic filter is not used.
has_subscription bit Whether the publication has any subscriptions. 0 means there are currently no subscriptions to this publication.
snapshot_in_default_folder Bit Specifies if the snapshot files are stored in the default folder. If 0, snapshot files can be found in the default folder. If 1, snapshot files will be stored in the alternate location specified by alt_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. That combination specifies that the snapshot files will be stored in both the default and alternate locations.
alt_snapshot_folder nvarchar(255) Specifies the location of the alternate folder for the snapshot.
pre_snapshot_script nvarchar(255) Specifies a pointer to an .sql file that the Merge Agent runs before any of the replicated object scripts when applying the snapshot at a Subscriber.
post_snapshot_script nvarchar(255) Specifies a pointer to an .sql file that the Merge Agent will run after all the other replicated object scripts and data have been applied during an initial synchronization.
compress_snapshot Bit Specifies that the snapshot that is written to the @alt_snapshot_folder location is compressed into the Microsoft® CAB format.
ftp_address sysname Is the network address of the FTP service for the Distributor. Specifies where publication snapshot files are located for the Merge Agent to pick up.
ftp_port int Is the port number of the FTP service for the Distributor. ftp_port has a default of 21. Specifies where the publication snapshot files are located for the Merge Agent to pick up.
ftp_subdirectory nvarchar(255) Specifies where the snapshot files will be available for the Merge Agent to pick up.
ftp_login sysname Is the username used to connect to the FTP service.
conflict_retention int Specifies the retention period, in days, for which conflicts are retained. After the specified number of days has passed, the conflict row is purged from the conflict table.
keep_partition_changes int Specifies whether synchronization optimization is occurring for this publication . keep_partition_changes has a default of 0. 0 means that synchronization is not optimized, and the partitions sent to all Subscribers will be verified when data changes in a partition. 1 means that synchronization is optimized, and only Subscribers having rows in the changed partition are affected. For more information, see Optimizing Synchronization.
allow_subscription_copy int Specifies whether the ability to copy the subscription databases that subscribe to this publication has been enabled. 0 means copying is not allowed.
allow_synctoalternate int Specifies whether an alternate synchronization partner is allowed to synchronize with this Publisher. 0 means a synchronization partner is not allowed.
validate_subscriber_info nvarchar(500) Lists the functions that are being used to retrieve Subscriber information and validate the dynamic filtering criteria on the Subscriber. Assists in verifying that the information is partitioned consistently with each merge.
backward_comp_level int Database compatibility level (60, 65, 70, and 80).
publish_to_activedirectory bit Specifies if the publication information is published to the Microsoft Active Directory™. 0 means the publication information is not available from the Microsoft Active Directory.
max_concurrent_merge int The 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.
max_concurrent_dynamic_snapshots int 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.


sp_helpmergepublication is used in merge replication.


Members of the db_owner fixed database role or the replmonitor role in the distribution database can execute sp_helpmergepublication. Members with entries in one or more publication access lists can also execute sp_helpmergepublication, however, they will only receive information on publications where they are listed in the publication access list.

See Also




System Stored Procedures

© 2016 Microsoft