Bewerken

Share via

sp_helppublication (Transact-SQL)

  • Artikel

Applies to: SQL Server Azure SQL Managed Instance

Returns information about a publication. For a SQL Server publication, this stored procedure is executed at the Publisher on the publication database. For an Oracle publication, this stored procedure is executed at the Distributor on any database.

Transact-SQL syntax conventions

Syntax

sp_helppublication_snapshot
    [ @publication = ] N'publication'
    [ , [ @publisher = ] N'publisher' ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication to be viewed. @publication is sysname, with a default of %, which returns information about all publications.

[ @publisher = ] N'publisher'

Specifies a non-SQL Server publisher. @publisher is sysname, with a default of NULL.

Note

publisher shouldn't be specified when requesting publication information from a SQL Server Publisher.

Result set

Column name Data type Description
pubid int ID for the publication.
name sysname Name of the publication.
restricted int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
status tinyint The current status of the publication.

0 = Inactive.
1 = Active.
task Used for backward compatibility.
replication frequency tinyint Type of replication frequency:

0 = Transactional
1 = Snapshot
synchronization method tinyint Synchronization mode:

0 = Native bulk copy program (bcp utility)
1 = Character bulk copy
3 = Concurrent, which means that native bulk copy (bcp utility) is used but tables aren't locked during the snapshot
4 = Concurrent_c, which means that character bulk copy is used but tables aren't locked during the snapshot
description nvarchar(255) Optional description for the publication.
immediate_sync bit Specifies whether the synchronization files are created or re-created each time the Snapshot Agent runs.
enabled_for_internet bit Specifies whether the synchronization files for the publication are exposed to the Internet, through file transfer protocol (FTP) and other services.
allow_push bit Specifies whether push subscriptions are allowed on the publication.
allow_pull bit Specifies whether pull subscriptions are allowed on the publication.
allow_anonymous bit Specifies whether anonymous subscriptions are allowed on the publication.
independent_agent bit Specifies whether there's a stand-alone Distribution Agent for this publication.
immediate_sync_ready bit Specifies whether the Snapshot Agent generated a snapshot that is ready to be used by new subscriptions. This parameter is defined only if the publication is set to always have a snapshot available for new or reinitialized subscriptions.
allow_sync_tran bit Specifies whether immediate-updating subscriptions are allowed on the publication.
autogen_sync_procs bit Specifies whether to automatically generate stored procedures to support immediate-updating subscriptions.
snapshot_jobid binary(16) Scheduled task ID.
retention int Amount of change, in hours, to save for the given publication.
has subscription bit Specifies whether the publication has an active subscription. 1 means that the publication has active subscriptions, and 0 means that the publication has no subscriptions.
allow_queued_tran bit Specifies whether disables queuing of changes at the Subscriber until they can be applied at the Publisher is enabled. If 0, changes at the Subscriber aren't queued.
snapshot_in_defaultfolder bit Specifies whether snapshot files are stored in the default folder. If 0, snapshot files are stored in the alternate location specified by alternate_snapshot_folder. If 1, snapshot files can be found in the default folder.
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 location. The Distribution Agent runs the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber.
post_snapshot_script nvarchar(255) Specifies a pointer to an .sql file location. The Distribution Agent will run the post-snapshot script after all the other replicated object scripts and data are applied during an initial synchronization.
compress_snapshot bit Specifies that the snapshot that is written to the alt_snapshot_folder location is to be compressed into the Microsoft CAB format. 0 specifies that the snapshot isn't compressed.
ftp_address sysname The network address of the FTP service for the Distributor. Specifies where publication snapshot files are located for the Distribution Agent or Merge Agent of a subscriber to pick up.
ftp_port int The port number of the FTP service for the Distributor.
ftp_subdirectory nvarchar(255) Specifies where the snapshot files are available for the Distribution Agent or Merge Agent of subscriber to pick up if the publication supports propagating snapshots using FTP.
ftp_login sysname The username used to connect to the FTP service.
allow_dts bit Specifies that the publication allows data transformations. 0 specifies that DTS transformations aren't allowed.
allow_subscription_copy bit Specifies whether the ability to copy the subscription databases that subscribe to this publication is enabled. 0 means that copying isn't allowed.
centralized_conflicts bit Specifies whether conflict records are stored on the Publisher:

0 = Conflict records are stored at both the publisher and at the subscriber that caused the conflict.
1 = Conflict records are stored at the Publisher.
conflict_retention int Specifies the conflict retention period, in days.
conflict_policy int Specifies the conflict resolution policy followed when the queued updating subscriber option is used. Can be one of these values:

1 = Publisher wins the conflict.
2 = Subscriber wins the conflict.
3 = Subscription is reinitialized.
queue_type Specifies which type of queue is used. Can be one of these values:

msmq = Use Microsoft Message Queuing to store transactions.
sql = Use SQL Server to store transactions.
Note: Support for Message Queuing is discontinued.
backward_comp_level Database compatibility level, and can be one of the following values:

90 = SQL Server 2005 (9.x)
100 = Microsoft SQL Server 2008 (10.0.x)
publish_to_AD bit Specifies whether the publication is published in the Microsoft Active Directory. A value of 1 indicates that it's published, and a value of 0 indicates that it's not published.
allow_initialize_from_backup bit Indicates if Subscribers can initialize a subscription to this publication from a backup rather than an initial snapshot. 1 means that subscriptions can be initialized from a backup, and 0 means that they can't. For more information, see Initialize a Transactional Subscription Without a Snapshot a transactional Subscriber without a snapshot.
replicate_ddl int Indicates if schema replication is supported for the publication. 1 indicates that data definition language (DDL) statements executed at the publisher are replicated, and 0 indicates that DDL statements aren't replicated. For more information, see Make Schema Changes on Publication Databases.
enabled_for_p2p int Specifies whether the publication can be used in a peer-to-peer replication topology. 1 indicates that the publication supports peer-to-peer replication. For more information, see Peer-to-Peer - Transactional Replication.
publish_local_changes_only int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
enabled_for_het_sub int Specifies whether the publication supports non-SQL Server Subscribers. A value of 1 means that non-SQL Server Subscribers are supported. A value of 0 means that only SQL Server Subscribers are supported. For more information, see Non-SQL Server Subscribers.
enabled_for_p2p_conflictdetection int Specifies whether the Distribution Agent detects conflicts for a publication that is enabled for peer-to-peer replication. A value of 1 means that conflicts are detected. For more information, see Peer-to-Peer - Conflict Detection in Peer-to-Peer Replication.
originator_id int Specifies an ID for a node in a peer-to-peer topology. This ID is used for conflict detection if enabled_for_p2p_conflictdetection is set to 1. For a list of IDs that are already used, query the MSpeer_originatorid_history system table.
p2p_continue_onconflict int Specifies whether The Distribution Agent continues to process changes when a conflict is detected. A value of 1 means that the agent continues to process changes.

Caution: We recommend that you use the default value of 0. When this option is set to 1, the Distribution Agent tries to converge data in the topology by applying the conflicting row from the node that's the highest originator ID. This method doesn't guarantee convergence. You should make sure that the topology is consistent after a conflict is detected. For more information, see "Handling Conflicts" in Peer-to-Peer - Conflict Detection in Peer-to-Peer Replication.
allow_partition_switch int Specifies whether ALTER TABLE...SWITCH statements can be executed against the published database. For more information, see Replicate Partitioned Tables and Indexes.
replicate_partition_switch int Specifies whether ALTER TABLE...SWITCH statements that are executed against the published database should be replicated to Subscribers. This option is valid only if allow_partition_switch is set to 1.
enabled_for_p2p_lastwriter_conflictdetection int Specifies whether the Distribution Agent detects Configure last writer conflict detection & resolution conflicts for a publication that is enabled for peer-to-peer replication. A value of 1 means that last writer conflicts are detected.

Applies to: SQL Server 2019 (15.x) CU 13 and later versions.

Return code values

0 (success) or 1 (failure).

Remarks

sp_helppublication is used in snapshot and transactional replication.

sp_helppublication returns information on all publications owned by the user executing this procedure.

Examples

DECLARE @myTranPub AS sysname
SET @myTranPub = N'AdvWorksProductTran' 

USE [AdventureWorks2022]
EXEC sp_helppublication @publication = @myTranPub
GO

Permissions

Only members of the sysadmin fixed server role at the Publisher or members of the db_owner fixed database role on the publication database or users in the publication access list (PAL) can execute sp_helppublication.

For a non-SQL Server Publisher, only members of the sysadmin fixed server role at the Distributor or members of the db_owner fixed database role on the distribution database or users in the PAL can execute sp_helppublication.

Related content