sp_helpsubscription (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_helpsubscription (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions

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

[ @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.

System_CAPS_ICON_note.jpg Note


publisher should not be specified, except when it is an Oracle Publisher.

Column nameData typeDescription
subscribersysnameName of the Subscriber.
publicationsysnameName of the publication.
articlesysnameName of the article.
destination databasesysnameName of the destination database in which replicated data is placed.
subscription statustinyintSubscription status:

 0 = Inactive

 1 = Subscribed

 2 = Active
synchronization typetinyintSubscription synchronization type:

 1 = Automatic

 2 = None
subscription typeintType of subscription:

 0 = Push

 1 = Pull

 2 = Anonymous
full subscriptionbitWhether subscription is to all articles in the publication:

 0 = No

 1 = Yes
subscription namenvarchar(255)Name of the subscription.
update modeint0 = Read-only

 1 = Immediate-updating subscription
distribution job idbinary(16)Job ID of the Distribution Agent.
loopback_detectionbitLoopback 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_enabledbitSpecifies 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_serversysnameName of the server enabled for remote agent activation. If NULL, then the current offload_server listed in MSdistribution_agents table is used.
dts_package_namesysnameSpecifies the name of the Data Transformation Services (DTS) package.
dts_package_locationintLocation 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_modesmallintIs the security mode at the Subscriber, where 1 means Windows Authentication, and 0 means SQL Server Authentication.
subscriber_loginsysnameIs the login name at the Subscriber.
subscriber_passwordActual Subscriber password is never returned. The result is masked by a "******" string.
job_loginsysnameName of the Windows account under which the Distribution Agent runs.
job_passwordActual job password is never returned. The result is masked by a "******" string.
distrib_agent_namenvarchar(100)Name of the agent job that synchronizes the subscription.
subscriber_typetinyintType of Subscriber, which can be one of the following:

 0 = SQL Server Subscriber

 1 = ODBC data source server

 2 = Microsoft JET database (deprecated)

 3 = OLE DB provider
subscriber_providersysnameUnique programmatic identifier (PROGID) with which the OLE DB provider for the non-SQL Server data source is registered.
subscriber_datasourcenvarchar(4000)Name of the data source as understood by the OLE DB provider.
subscriber_providerstringnvarchar(4000)OLE DB provider-specific connection string that identifies the data source.
subscriber_locationnvarchar(4000)Location of the database as understood by the OLE DB provider
subscriber_catalogsysnameCatalog to be used when making a connection to the OLE DB provider.

0 (success) or 1 (failure)

sp_helpsubscription is used in snapshot and transactional replication.

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.

sp_addsubscription (Transact-SQL)
sp_changesubstatus (Transact-SQL)
sp_dropsubscription (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft