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.
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.
|push (default)||Push 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.|
Return Code Values
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.