Export (0) Print
Expand All
Expand Minimize

sp_helpmergesubscription (Transact-SQL)

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:
© 2014 Microsoft