Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_helpmergepullsubscription (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft