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

sp_helppullsubscription (Transact-SQL)

 

Applies To: SQL Server

Displays information about one or more subscriptions at the Subscriber. This stored procedure is executed at the Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions

  
sp_helppullsubscription [ [ @publisher = ] 'publisher' ]  
    [ , [ @publisher_db = ] 'publisher_db' ]   
    [ , [ @publication = ] 'publication' ]  
    [ , [ @show_push = ] 'show_push' ]  

[ @publisher=] 'publisher'
Is the name of the remote server. publisher is sysname, with a default of %, which returns information for all Publishers.

[ @publisher_db=] 'publisher_db'
Is the name of the Publisher database. publisher_db is sysname, with a default of %, which returns all the Publisher databases.

[ @publication=] 'publication'
Is the name of the publication. publication is sysname, with a default of %, which returns all the publications. If this parameter equals to ALL, only pull subscriptions with independent_agent = 0 are returned.

[ @show_push=] 'show_push'
Is whether all push subscriptions are to be returned. show_pushis nvarchar(5), with a default of FALSE, which does not return push subscriptions.

Column nameData typeDescription
publishersysnameName of the Publisher.
publisher databasesysnameName of the Publisher database.
publicationsysnameName of the publication.
independent_agentbitIndicates whether there is a stand-alone Distribution Agent for this publication.
subscription typeintSubscription type to the publication.
distribution agentnvarchar(100)Distribution Agent handling the subscription.
publication descriptionnvarchar(255)Description of the publication.
last updating timedateTime the subscription information was updated. This is a UNICODE string of ISO date (114) + ODBC time (121). The format is yyyymmdd hh:mi:sss.mmm where 'yyyy' is year, 'mm' is month, 'dd' is day, 'hh' is hour, 'mi' is minute, 'sss' is seconds, and 'mmm' is milliseconds.
subscription namevarchar(386)Name of the subscription.
last transaction timestampvarbinary(16)Timestamp of the last replicated transaction.
update modetinyintType of updates allowed.
distribution agent job_idintJob ID of the Distribution Agent.
enabled_for_synmgrintWhether the subscription can be synchronized through the Microsoft Synchronization Manager.
subscription guidbinary(16)Global identifier for the version of the subscription on the publication.
subidbinary(16)Global identifier for an anonymous subscription.
immediate_syncbitWhether the synchronization files are created or re-created each time the Snapshot Agent runs.
publisher loginsysnameLogin ID used at the Publisher for SQL Server Authentication.
publisher passwordnvarchar(524)Password (encrypted) used at the Publisher for SQL Server Authentication.
publisher security_modeintSecurity mode implemented at the Publisher:

 0 = SQL Server Authentication

 1 = Windows Authentication

 2 = The synchronization triggers use a static sysservers entry to do remote procedure call (RPC), and publisher must be defined in the sysservers table as a remote server or linked server.
distributorsysnameName of the Distributor.
distributor_loginsysnameLogin ID used at the Distributor for SQL Server Authentication.
distributor_passwordnvarchar(524)Password (encrypted) used at the Distributor for SQL Server Authentication.
distributor_security_modeintSecurity mode implemented at the Distributor:

 0 = SQL Server Authentication

 1 = Windows Authentication
ftp_addresssysnameFor backward compatibility only.
ftp_portintFor backward compatibility only.
ftp_loginsysnameFor backward compatibility only.
ftp_passwordnvarchar(524)For backward compatibility only.
alt_snapshot_foldernvarchar(255)Location where snapshot folder is stored if the location is other than or in addition to the default location.
working_directorynvarchar(255)Fully qualified path to the directory where snapshot files are transferred using File Transfer Protocol (FTP) when that option is specified.
use_ftpbitSubscription is subscribing to Publication over the Internet and FTP addressing properties are configured. If 0, Subscription is not using FTP. If 1, subscription is using FTP.
publication_typeintSpecifies the replication type of the publication:

 0 = Transactional replication

 1 = Snapshot replication

 2 = Merge replication
dts_package_namesysnameSpecifies the name of the Data Transformation Services (DTS) package.
dts_package_locationintLocation where the DTS package is stored:

 0 = Distributor

 1 = Subscriber
offload_agentbitSpecifies if the agent can be activated remotely. If 0, the agent cannot be activated remotely.
offload_serversysnameSpecifies the network name of the server used for remote activation.
last_sync_statusintSubscription status:

 0 = All jobs are waiting to start

 1 = One or more jobs are starting

 2 = All jobs have executed successfully

 3 = At least one job is executing

 4 = All jobs are scheduled and idle

 5 = At least one job is attempting to execute after a previous failure

 6 = At least one job has failed to execute successfully
last_sync_summarysysnameDescription of last synchronization results.
last_sync_timedatetimeTime the subscription information was updated. This is a UNICODE string of ISO date (114) + ODBC time (121). The format is yyyymmdd hh:mi:sss.mmm where 'yyyy' is year, 'mm' is month, 'dd' is day, 'hh' is hour, 'mi' is minute, 'sss' is seconds, and 'mmm' is milliseconds.
job_loginnvarchar(512)Is the Windows account under which the Distribution agent runs, which is returned in the format domain\username.
job_passwordsysnameFor security reasons, a value of "**********" is always returned.

0 (success) or 1 (failure)

sp_helppullsubscription is used in snapshot and transactional replication.

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_helppullsubscription .

sp_addpullsubscription (Transact-SQL)
sp_droppullsubscription (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft