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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_helpmergesubscription [ [ @publication=] 'publication']
[ , [ @subscriber=] 'subscriber']
[ , [ @subscriber_db=] 'subscriber_db']
[ , [ @publisher=] 'publisher']
[ , [ @publisher_db=] 'publisher_db']
[ , [ @subscription_type=] 'subscription_type']
[ , [ @found=] 'found' OUTPUT]
Arguments
- [ @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.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| 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. |
Remarks
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.
Permissions
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.
See Also
Reference
sp_addmergesubscription (Transact-SQL)
sp_changemergesubscription (Transact-SQL)
sp_dropmergesubscription (Transact-SQL)
System Stored Procedures (Transact-SQL)