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.
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:
|
||||||||||
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. |
Return Code Values
0 (success) or 1 (failure)
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)