View and Modify Pull Subscription Properties
Applies to:
SQL Server
Azure SQL Managed Instance
This topic describes how to view and modify pull subscription properties in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).
In This Topic
To view and modify pull subscription properties, using:
Using SQL Server Management Studio
View pull subscription properties from the Publisher or the Subscriber in the Subscription Properties - <Publisher>: <PublicationDatabase> dialog box, which is available from SQL Server Management Studio. More properties are visible from the Subscriber, and properties can be modified at the Subscriber. You can also view properties from the Publisher on the All Subscriptions tab, which is available in Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.
To view pull subscription properties from the Publisher in Management Studio
Connect to the Publisher in Management Studio, and then expand the server node.
Expand the Replication folder, and then expand the Local Publications folder.
Expand the appropriate publication, right-click a subscription, and then click Properties.
View properties, and then click OK.
To view and modify pull subscription properties from the Subscriber in Management Studio
Connect to the Subscriber in Management Studio, and then expand the server node.
Expand the Replication folder, and then expand the Local Subscriptions folder.
Right-click a subscription, and then click Properties.
Modify any properties if necessary, and then click OK.
To view pull subscription properties from the Publisher in Replication Monitor
Expand a Publisher group in the left pane of Replication Monitor, expand a Publisher, and then click a publication.
Click the All Subscriptions tab.
Right-click a subscription, and then click Properties.
View properties, and then click OK.
Using Transact-SQL
Pull subscriptions can be modified and their properties accessed programmatically using replication stored procedures. The stored procedures used depend on the type of publication to which the subscription belongs.
To view the properties of a pull subscription to a snapshot or transactional publication
At the Subscriber, execute sp_helppullsubscription. Specify
@publisher
,@publisher_db
, and@publication
. This returns information about the subscription that is stored in system tables at the Subscriber.At the Subscriber, execute sp_helpsubscription_properties. Specify
@publisher
,@publisher_db
,@publication
, and one of the following values for@publication_type
:0 - Subscription belongs to a transactional publication.
1 - Subscription belongs to a snapshot publication.
At the Publisher, execute sp_helpsubscription. Specify
@publication
and@subscriber
.At the Publisher, execute sp_helpsubscriberinfo, specifying
@subscriber
. This displays information about the Subscriber.
To change the properties of a pull subscription to a snapshot or transactional publication
At the Subscriber, execute sp_change_subscription_properties, specifying
@publisher
,@publisher_db
,@publication
, a value of either 0 (transactional) or 1 (snapshot) for@publication_type
, the subscription property being changed as@property
, and the new value as@value
.(Optional) At the Subscriber on the subscription database, execute sp_changesubscriptiondtsinfo. Specify the ID of the Distribution Agent job for
@jobid
, and the following Data Transformation Services (DTS) package properties:@dts_package_name
dts_package_password
@dts_package_location
This changes the DTS package properties of a subscription.
Note
The job ID can be obtained by executing sp_helpsubscription.
To view the properties of a pull subscription to a merge publication
At the Subscriber, execute sp_helpmergepullsubscription. Specify
@publisher
,@publisher_db
, and@publication
.At the Subscriber, execute sp_helpsubscription_properties. Specify
@publisher
,@publisher_db
,@publication
, and a value of 2 for@publication_type
.At the Publisher, execute sp_helpmergesubscription to display subscription information. To return information on a specific subscription, you must specify
@publication
,@subscriber
, and a value of pull for @subscription_type.At the Publisher, execute sp_helpsubscriberinfo, specifying
@subscriber
. This displays information about the Subscriber.
To change the properties of a pull subscription to a merge publication
- At the Subscriber, execute sp_changemergepullsubscription. Specify
@publication
,@publisher
,@publisher_db
, the subscription property being changed as@property
, and the new value as@value
.
Using Replication Management Objects (RMO)
The RMO classes you use to view or modify pull subscription properties depend on the type of publication to which the pull subscription is subscribed.
To view or modify properties of a pull subscription to a snapshot or transactional publication
Create a connection to the Subscriber by using the ServerConnection class.
Create an instance of the TransPullSubscription class.
Set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties.
Set the connection from step 1 for the ConnectionContext property.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist on the server.
(Optional) To change properties, set a new value for one of the TransPullSubscription properties that can be set, and then call the CommitPropertyChanges method.
(Optional) To view the new settings, call the Refresh method to reload the properties for the article.
Close all connections.
To view or modify properties of a pull subscription to a merge publication
Create a connection to the Subscriber by using the ServerConnection class.
Create an instance of the MergePullSubscription class.
Set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties.
Set the connection from step 1 for the ConnectionContext property.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist on the server.
(Optional) To change properties, set a new value for one of the MergePullSubscription properties that can be set, and then call the CommitPropertyChanges method.
(Optional) To view the new settings, call the Refresh method to reload the properties for the article.
Close all connections.
See Also
View information and perform tasks using Replication Monitor
Replication Security Best Practices
Subscribe to Publications