View and Modify Push Subscription Properties
Applies to: SQL Server Azure SQL Database
This topic describes how to view and modify push subscription properties in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).
Note
Azure SQL Managed Instance can be a publisher, distributor, and subscriber for snapshot and transactional replication. Databases in Azure SQL Database can only be push subscribers for snapshot and transactional replication. For more information, see Transactional replication with Azure SQL Database and Azure SQL Managed Instance.
View and modify push subscription properties from the Publisher in:
The Subscription Properties - <Publisher>: <PublicationDatabase> dialog box, which is available from SQL Server Management Studio.
The All Subscriptions tab, which is available in Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.
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.
Modify any properties if necessary, and then click OK.
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.
Modify any properties if necessary, and then click OK.
Push 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.
At the Publisher on the publication database, execute sp_helpsubscription. Specify @publication, @subscriber, and a value of all for @article.
At the Publisher on the publication database, execute sp_helpsubscriberinfo, specifying @subscriber.
At the Publisher on the publication database, execute sp_changesubscriber, specifying @subscriber and any parameters for the Subscriber properties being changed.
At the Publisher on the publication database, execute sp_changesubscription. Specify @publication, @subscriber, @destination_db, a value of all for @article, the subscription property being changed as @property, and the new value as @value. This changes security settings for the push subscription.
(Optional) To change the Data Transformation Services (DTS) package properties of a subscription, execute sp_changesubscriptiondtsinfo at the Subscriber on the subscription database. Specify the ID of the Distribution Agent job for @jobid and the following 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.
At the Publisher on the publication database, execute sp_helpmergesubscription. Specify @publication and @subscriber.
At the Publisher, execute sp_helpsubscriberinfo, specifying @subscriber.
- At the Publisher on the publication database, execute sp_changemergesubscription. Specify @publication, @subscriber, @subscriber_db, the subscription property being changed as @property, and the new value as @value.
The RMO classes you use to view or modify push subscription properties depend on the type of publication to which the push subscription is subscribed.
Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the TransSubscription class.
Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties.
Set the ServerConnection from step 1 for the ConnectionContext property setting.
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.
(Optional) To change properties, set a new value for one of the TransSubscription 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 subscription.
Create a connection to the Subscriber by using the ServerConnection class.
Create an instance of the MergeSubscription class.
Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties.
Set the ServerConnection from step 1 for the ConnectionContext property setting.
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.
(Optional) To change properties, set a new value for one of the MergeSubscription 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 subscription.