This topic has not yet been rated - Rate this topic

sp_helpsubscription (Transact-SQL)

SQL Server 2012

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

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:

Value	Description
0	SQL Server Subscriber
1	ODBC data source server
2	Microsoft JET database (deprecated)
3	OLE 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.

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.

Reference

sp_addsubscription (Transact-SQL)

sp_changesubstatus (Transact-SQL)

sp_dropsubscription (Transact-SQL)

System Stored Procedures (Transact-SQL)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD