sp_helpmergepullsubscription (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Argument

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

Result Sets

Column name

Data type

Description

subscription_name

nvarchar(1000)

Name of the subscription.

publication

sysname

Name of the publication.

publisher

sysname

Name of the Publisher.

publisher_db

sysname

Name of the Publisher database.

subscriber

sysname

Name of the Subscriber.

subscription_db

sysname

Name of the subscription database.

status

int

Subscription 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_type

int

Type of Subscriber:

1 = Global

2 = Local

3 = Anonymous

subscription_type

int

Type of subscription:

0 = Push

1 = Pull

2 = Anonymous

priority

float(8)

Subscription priority. The value must be less than 100.00.

sync_type

tinyint

Subscription synchronization type:

1 = Automatic

2 = Snapshot is not used.

description

nvarchar(255)

Brief description of the pull subscription.

merge_jobid

binary(16)

Job ID of the Merge Agent.

enabled_for_syncmgr

int

Whether the subscription can be synchronized through the Microsoft Synchronization Manager.

last_updated

nvarchar(26)

Time that the Merge Agent last successfully synchronized the subscription.

publisher_login

sysname

The Publisher login name.

publisher_password

sysname

The Publisher password.

publisher_security_mode

int

Specifies the security mode of the Publisher:

0 = SQL Server Authentication

1 = Windows Authentication

distributor

sysname

Name of the Distributor.

distributor_login

sysname

The Distributor login name.

distributor_password

sysname

The Distributor password.

distributor_security_mode

int

Specifies the security mode of the Distributor:

0 = SQL Server Authentication

1 = Windows Authentication

ftp_address

sysname

Available for backward compatibility only. Is the network address of the file transfer protocol (FTP) service for the Distributor.

ftp_port

int

Available for backward compatibility only. Is the port number of the FTP service for the Distributor.

ftp_login

sysname

Available for backward compatibility only. Is the username used to connect to the FTP service.

ftp_password

sysname

Available for backward compatibility only. Is the user password used to connect to the FTP service.

alt_snapshot_folder

nvarchar(255)

Location where snapshot folder is stored if the location is other than or in addition to the default location.

working_directory

nvarchar(255)

Fully-qualified path to the directory where snapshot files are transferred using FTP when that option is specified.

use_ftp

bit

Subscription 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_agent

bit

Specifies if the agent can be activated and run remotely. If 0, the agent cannot be remotely activated.

offload_server

sysname

Name of the server used for remote activation.

use_interactive_resolver

int

Returns whether or not the interactive resolver is used during reconciliation. If 0, the interactive resolver is not used.

subid

uniqueidentifier

ID of the Subscriber.

dynamic_snapshot_location

nvarchar(255)

The path to the folder where the snapshot files are saved.

last_sync_status

int

Synchronization 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_summary

sysname

Description of last synchronization results.

use_web_sync

bit

Specifies if the subscription can be synchronized over HTTPS, where a value of 1 means that this feature is enabled.

internet_url

nvarchar(260)

URL that represents the location of the replication listener for Web synchronization.

internet_login

nvarchar(128)

Login that the Merge Agent uses when connecting to the Web server that is hosting Web synchronization using Basic Authentication.

internet_password

nvarchar(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_mode

int

The 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_timeout

int

Length of time, in seconds, before a Web synchronization request expires.

hostname

nvarchar(128)

Specifies an overloaded value for HOST_NAME when this function is used in the WHERE clause of a parameterized row filter.

job_login

nvarchar(512)

Is the Windows account under which the Merge agent runs, which is returned in the format domain\username.

job_password

sysname

For security reasons, a value of "**********" is always returned.

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Permissions

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