Transact-SQL Reference

sp_helpsubscription

Lists subscription information associated with a particular publication, article, Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher on the publication database.

Syntax

sp_helpsubscription [ [ @publication = ] 'publication' ]
    [ , [ @article = ] 'article' ]
    [ , [ @subscriber = ] 'subscriber' ]
    [ , [ @destination_db = ] 'destination_db' ]
    [ , [ @found =] found OUTPUT ]

Arguments

[@publication = ] 'publication'

Is the name of the associated publication. publication is sysname, with a default of %, which returns all subscription information for this server.

[@article = ] 'article'

Is the name of the article. article is sysname, with a default of %, which returns all subscription information for the selected publications and Subscribers. If all, only one entry is returned for the full subscription on a publication.

[@subscriber = ] 'subscriber'

Is the name of the Subscriber on which to obtain subscription information. subscriber is sysname, with a default of %, which returns all subscription information for the selected publications and articles.

[@destination_db = ] 'destination_db'

Is the name of the destination database. destination_db is sysname, with a default of %.

[@found = ] 'found' OUTPUT

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

Result Sets

Column name	Data type	Description
subscriber	sysname	Name of the Subscriber.
publication	sysname	Name of the publication.
article	sysname	Name of the article.
destination database	sysname	Name of the destination database in which replicated data is placed.
subscription status	tinyint	Subscription status: 0 = Inactive 1 = Subscribed 2 = Active
synchronization type	tinyint	Subscription synchronization type: 1 = Automatic 2 = None
subscription type	int	Type of subscription: 0 = Push 1 = Pull 2 = Anonymous
full subscription	bit	Whether subscription is to all articles in the publication: 0 = No 1 = Yes
subscription name	nvarchar(255)	Name of the subscription.
update mode	int	0 = Read-only 1 = Immediate-updating subscription
distribution job id	binary(16)	Job ID of the Distribution Agent.
loopback_detection	bit	0 = No 1 = Yes
offload_enabled	bit	Specifies whether offload execution of a replication agent has been set to run at the Subscriber. If 0, agent is run at the Publisher. If 1, agent is run at the Subscriber.
offload_server	sysname	Name of the server enabled for remote agent activation. If NULL, then the current offload_server listed in MSDistribution_agents table is used.
dts_package_name	sysname	Specifies the name of the DTS package.
dts_package_location	int	Location of the DTS package, if one is assigned to the subscription. If there is a package, a value of 0 specifies the package location at the distributor. A value of 1 specifies the subscriber.

Remarks

sp_helpsubscription is used in snapshot, transactional, and merge replication.

Permissions