sp_helpsubscription

Transact-SQL Reference

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

Execute permissions default to the public role. However, sysadmin fixed server role or db_owner fixed database role can see all subscriptions, while the other users get a result set listing only their own subscriptions.

See Also

sp_addsubscription

sp_changesubstatus

sp_dropsubscription

System Stored Procedures