View and Modify Pull Subscription Properties

Article
03/01/2023

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:

SQL Server Management Studio

Transact-SQL

Replication Management Objects (RMO)

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.