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

sp_helpmergesubscription (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 a subscription to a merge publication, both push and pull. This stored procedure is executed at the Publisher on the publication database or at a republishing Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions

sp_helpmergesubscription [ [ @publication=] 'publication']
    [ , [ @subscriber=] 'subscriber']
    [ , [ @subscriber_db=] 'subscriber_db']
    [ , [ @publisher=] 'publisher']
    [ , [ @publisher_db=] 'publisher_db']
    [ , [ @subscription_type=] 'subscription_type']
    [ , [ @found=] 'found' OUTPUT]

[ @publication=] 'publication'

Is the name of the publication. publication is sysname, with a default of %. The publication must already exist and conform to the rules for identifiers. If NULL or %, information about all merge publications and subscriptions in the current database is returned.

[ @subscriber=] 'subscriber'

Is the name of the Subscriber. subscriber is sysname, with a default of %. If NULL or %, information about all subscriptions to the given publication is returned.

[ @subscriber_db=] 'subscriber_db'

Is the name of the subscription database. subscriber_dbis sysname, with a default of %, which returns information about all subscription databases.

[ @publisher=] 'publisher'

Is the name of the Publisher. The Publisher must be a valid server. publisheris sysname, with a default of %, which returns information about all Publishers.

[ @publisher_db=] 'publisher_db'

Is the name of the Publisher database. publisher_dbis sysname, with a default of %, which returns information about all Publisher databases.

[ @subscription_type=] 'subscription_type'

Is the type of subscription. subscription_typeis nvarchar(15), and can be one of these values.

Value

Description

push (default)

Push subscription

pull

Pull subscription

both

Both a push and pull subscription

[ @found=] 'found'OUTPUT

Is a flag to indicate returning rows. foundis int and an OUTPUT parameter, with a default of NULL. 1 indicates the publication is found. 0 indicates the publication is not found.

Column name

Data type

Description

subscription_name

sysname

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.

subscriber_db

sysname

Name of the subscription database.

status

int

Status of the subscription:

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

subscriber_type

int

Type of Subscriber.

subscription_type

int

Type of subscription:

0 = Push

1 = Pull

2 = Both

priority

float(8)

Number indicating the priority for the subscription.

sync_type

tinyint

Subscription sync type.

description

nvarchar(255)

Brief description of this merge subscription.

merge_jobid

binary(16)

Job ID of the Merge Agent.

full_publication

tinyint

Whether the subscription is to a full or filtered publication.

offload_enabled

bit

Specifies if offload execution of a replication agent has been set to run at the Subscriber. If NULL, execution is run at the Publisher.

offload_server

sysname

Name of the server to where the agent is running.

use_interactive_resolver

int

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

hostname

sysname

Value supplied when a subscription is filtered by the value of the HOST_NAME function.

subscriber_security_mode

smallint

Is the security mode at the Subscriber, where 1 means Windows Authentication, and 0 means Microsoft SQL Server Authentication.

subscriber_login

sysname

Is the login name at the Subscriber.

subscriber_password

sysname

Actual Subscriber password is never returned. The result is masked by a "******" string.

0 (success) or 1 (failure)

sp_helpmergesubscription is used in merge replication to return subscription information stored at the Publisher or republishing Subscriber.

For anonymous subscriptions, the subscription_typevalue is always 1 (pull). However, you must execute sp_helpmergepullsubscription at the Subscriber for information on anonymous subscriptions.

Only members of the sysadmin fixed server role, the db_owner fixed database role or the publication access list for the publication to which the subscription belongs can execute sp_helpmergesubscription.

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

Community Additions

ADD
Show:
© 2015 Microsoft