sp_helppublication (Transact-SQL)

Returns information about a publication. For a Microsoft 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 [ [ @publication = ] 'publication' ]
    [ , [ @found=] found OUTPUT]
    [ , [ @publisher = ] 'publisher' ]

Arguments

• [ @publication = ] 'publication'
  Is the name of the publication to be viewed. publication is sysname, with a default of %, which returns information about all publications.

• [ @found = ] 'found' OUTPUT
  Is a flag to indicate returning rows. foundis int and an OUTPUT parameter, with a default of 23456. 1 indicates the publication is found. 0 indicates the publication is not found.

• [ @publisher = ] 'publisher'
  Specifies a non-SQL Server publisher. publisher is sysname, with a default of NULL.

  Note

  publisher should not be specified when requesting publication information from a SQL Server Publisher.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name

Data type

Description

pubid

int

ID for the publication.

name

sysname

Name of the publication.

restricted

int

Reserved for future use.

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 are not locked during the snapshot

4 = Concurrent_c, which means that character bulk copy is used but tables are not locked during the snapshot

description

nvarchar(255)

Optional description for the publication.

immediate_sync

bit

Whether the synchronization files are created or re-created each time the Snapshot Agent runs.

enabled_for_internet

bit

Whether the synchronization files for the publication are exposed to the Internet, through file transfer protocol (FTP) and other services.

allow_push

bit

Whether push subscriptions are allowed on the publication.

allow_pull

bit

Whether pull subscriptions are allowed on the publication.

allow_anonymous

bit

Whether anonymous subscriptions are allowed on the publication.

independent_agent

bit

Whether there is a stand-alone Distribution Agent for this publication.

immediate_sync_ready

bit

Whether or not 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

Whether immediate-updating subscriptions are allowed on the publication.

autogen_sync_procs

bit

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

If the publication has an active subscriptions. 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 has been enabled. If 0, changes at the Subscriber are not queued.

snapshot_in_defaultfolder

bit

Specifies whether snapshot files are stored in the default folder. If 0, snapshot files have been 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 will run 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 have been 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 will not be 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 will be 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 are not allowed.

allow_subscription_copy

bit

Specifies whether the ability to copy the subscription databases that subscribe to this publication has been enabled. 0 means that copying is not 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 has been discontinued.

backward_comp_level

 

Database compatibility level, and can be one of the following:

10 = Microsoft SQL Server 7.0

20 = SQL Server 7.0 Service Pack 1

30 = SQL Server 7.0 Service Pack 2

35 = SQL Server 7.0 Service Pack 3

40 = Microsoft SQL Server 2000

50 = SQL Server 2000 Service Pack 1

60 = SQL Server 2000 Service Pack 3

90 = Microsoft SQL Server 2005

publish_to_AD

bit

Specifies whether the publication is published in the Microsoft Active Directory™. A value of 1 indicates that it is published, and a value of 0 indicates that it is 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 cannot. For more information, see Initializing 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 are not replicated. For more information, see Making Schema Changes on Publication Databases.

enabled_for_p2p

int

If 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

For internal use only.

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.

Remarks

sp_helppublication is used in snapshot and transactional replication.

sp_helppublication will return information on all publications that are owned by the user executing this procedure.

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.

Example

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

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

See Also

Reference

sp_addpublication (Transact-SQL)
sp_changepublication (Transact-SQL)
sp_droppublication (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Other Resources

How to: View and Modify Publication Properties (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance