sp_helpsubscription (Transact-SQL)
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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_helpsubscription [ [ @publication = ] 'publication' ]
[ , [ @article = ] 'article' ]
[ , [ @subscriber = ] 'subscriber' ]
[ , [ @destination_db = ] 'destination_db' ]
[ , [ @found=] found OUTPUT ]
[ , [ @publisher = ] 'publisher' ]
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. 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'
Is the name of the Publisher. publisher is sysname, and defaults to the name of the current server.Note
publisher should not be specified, except when it is an Oracle Publisher.
Return Code Values
0 (success) or 1 (failure)
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
Loopback detection determines whether the Distribution Agent sends transactions originated at the Subscriber back to the Subscriber:
0 = Sends back.
1 = Does not send back.
Used with bidirectional transactional replication. For more information, see Bidirectional Transactional Replication.
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 Data Transformation Services (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.
subscriber_security_mode
smallint
Is the security mode at the Subscriber, where 1 means Windows Authentication, and 0 means SQL Server Authentication.
subscriber_login
sysname
Is the login name at the Subscriber.
subscriber_password
Actual Subscriber password is never returned. The result is masked by a "******" string.
job_login
sysname
Name of the Windows account under which the Distribution Agent runs.
job_password
Actual job password is never returned. The result is masked by a "******" string.
distrib_agent_name
nvarchar(100)
Name of the agent job that synchronizes the subscription.
subscriber_type
tinyint
Type of Subscriber, which can be one of the following:
Value
Description
0SQL Server Subscriber
1ODBC data source server
2Microsoft JET database (deprecated)
3OLE DB provider
subscriber_provider
sysname
Unique programmatic identifier (PROGID) with which the OLE DB provider for the non-SQL Server data source is registered.
subscriber_datasource
nvarchar(4000)
Name of the data source as understood by the OLE DB provider.
subscriber_providerstring
nvarchar(4000)
OLE DB provider-specific connection string that identifies the data source.
subscriber_location
nvarchar(4000)
Location of the database as understood by the OLE DB provider
subscriber_catalog
sysname
Catalog to be used when making a connection to the OLE DB provider.
Remarks
sp_helpsubscription is used in snapshot and transactional replication.
Permissions
Execute permissions default to the public role. Users are only returned information for subscriptions that they created. Information on all subscriptions is returned to members of the sysadmin fixed server role at the Publisher or members of the db_owner fixed database role on the publication database.
See Also
Reference
sp_addsubscription (Transact-SQL)
sp_changesubstatus (Transact-SQL)
sp_dropsubscription (Transact-SQL)
System Stored Procedures (Transact-SQL)