sp_helpmergepullsubscription (Transact-SQL)


Applies To: SQL Server

Returns information about pull subscriptions that exist at a Subscriber. This stored procedure is executed at the Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions

sp_helpmergepullsubscription [ [ @publication=] 'publication']  
    [ , [ @publisher=] 'publisher']  
    [ , [ @publisher_db=] 'publisher_db']  
    [ , [ @subscription_type=] 'subscription_type']  

[ @publication=] 'publication'
Is the name of the publication. publication is sysname, with a default of %. If publication is %, information about all merge publications and subscriptions in the current database is returned.

[ @publisher=] 'publisher'
Is the name of the Publisher. publisheris sysname, with a default of %.

[ @publisher_db=] 'publisher_db'
Is the name of the Publisher database. publisher_dbis sysname, with a default of %.

[ @subscription_type=] 'subscription_type'
Is whether to show pull subscriptions. subscription_typeis nvarchar(10), with a default of 'pull'. Valid values are 'push', 'pull', or 'both'.

Column nameData typeDescription
subscription_namenvarchar(1000)Name of the subscription.
publicationsysnameName of the publication.
publishersysnameName of the Publisher.
publisher_dbsysnameName of the Publisher database.
subscribersysnameName of the Subscriber.
subscription_dbsysnameName of the subscription database.
statusintSubscription status:

 0 = Inactive subscription

 1 = Active subscription

 2 = Deleted subscription

 3 = Detached subscription

 4 = Attached subscription

 5 = Subscription has been marked for reinitialization with upload

 6 = Attaching the subscription failed

 7 = Subscription restored from backup
subscriber_typeintType of Subscriber:

 1 = Global

 2 = Local

 3 = Anonymous
subscription_typeintType of subscription:

 0 = Push

 1 = Pull

 2 = Anonymous
priorityfloat(8)Subscription priority. The value must be less than 100.00.
sync_typetinyintSubscription synchronization type:

 1 = Automatic

 2 = Snapshot is not used.
descriptionnvarchar(255)Brief description of the pull subscription.
merge_jobidbinary(16)Job ID of the Merge Agent.
enabled_for_syncmgrintWhether the subscription can be synchronized through the Microsoft Synchronization Manager.
last_updatednvarchar(26)Time that the Merge Agent last successfully synchronized the subscription.
publisher_loginsysnameThe Publisher login name.
publisher_passwordsysnameThe Publisher password.
publisher_security_modeintSpecifies the security mode of the Publisher:

 0 = SQL Server Authentication

 1 = Windows Authentication
distributorsysnameName of the Distributor.
distributor_loginsysnameThe Distributor login name.
distributor_passwordsysnameThe Distributor password.
distributor_security_modeintSpecifies the security mode of the Distributor:

 0 = SQL Server Authentication

 1 = Windows Authentication
ftp_addresssysnameAvailable for backward compatibility only. Is the network address of the file transfer protocol (FTP) service for the Distributor.
ftp_portintAvailable for backward compatibility only. Is the port number of the FTP service for the Distributor.
ftp_loginsysnameAvailable for backward compatibility only. Is the username used to connect to the FTP service.
ftp_passwordsysnameAvailable for backward compatibility only. Is the user password used to connect to the FTP service.
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 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.
offload_agentbitSpecifies if the agent can be activated and run remotely. If 0, the agent cannot be remotely activated.
offload_serversysnameName of the server used for remote activation.
use_interactive_resolverintReturns whether or not the interactive resolver is used during reconciliation. If 0, the interactive resolver is not used.
subiduniqueidentifierID of the Subscriber.
dynamic_snapshot_locationnvarchar(255)The path to the folder where the snapshot files are saved.
last_sync_statusintSynchronization status:

 1 = Starting

 2 = Succeeded

 3 = In progress

 4 = Idle

 5 = Retrying after a previous failure

 6 = Failed

 7 = Failed validation

 8 = Passed validation

 9 = Requested a shutdown
last_sync_summarysysnameDescription of last synchronization results.
use_web_syncbitSpecifies if the subscription can be synchronized over HTTPS, where a value of 1 means that this feature is enabled.
internet_urlnvarchar(260)URL that represents the location of the replication listener for Web synchronization.
internet_loginnvarchar(128)Login that the Merge Agent uses when connecting to the Web server that is hosting Web synchronization using Basic Authentication.
internet_passwordnvarchar(524)Password for the login that the Merge Agent uses when connecting to the Web server that is hosting Web synchronization using Basic Authentication.
internet_security_modeintThe authentication mode used when connecting to the Web server that is hosting Web synchronization. A value of 1 means Windows Authentication, and a value of 0 means SQL Server Authentication.
internet_timeoutintLength of time, in seconds, before a Web synchronization request expires.
hostnamenvarchar(128)Specifies an overloaded value for HOST_NAME when this function is used in the WHERE clause of a parameterized row filter.
job_loginnvarchar(512)Is the Windows account under which the Merge 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_helpmergepullsubscription is used in merge replication. In the result set, the date returned in last_updated is formatted as YYYYMMDD hh:mm:ss.fff.

Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_helpmergepullsubscription.

sp_addmergepullsubscription (Transact-SQL)
sp_changemergepullsubscription (Transact-SQL)
sp_dropmergepullsubscription (Transact-SQL)
Replication Stored Procedures (Transact-SQL)

Community Additions