แก้ไข

แชร์ผ่าน

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.

Using SQL Server Management Studio

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.

To view and modify push subscription properties in Management Studio

  1. Connect to the Publisher in Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Expand the appropriate publication, right-click a subscription, and then click Properties.

  4. Modify any properties if necessary, and then click OK.

To view and modify push subscription properties in Replication Monitor

  1. Expand a Publisher group in the left pane of Replication Monitor, expand a Publisher, and then click a publication.

  2. Click the All Subscriptions tab.

  3. Right-click a subscription, and then click Properties.

  4. Modify any properties if necessary, and then click OK.

Using Transact-SQL

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.

To view the properties of a push subscription to a snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_helpsubscription. Specify @publication, @subscriber, and a value of all for @article.

  2. At the Publisher on the publication database, execute sp_helpsubscriberinfo, specifying @subscriber.

To change the properties of a push subscription to a snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_changesubscriber, specifying @subscriber and any parameters for the Subscriber properties being changed.

  2. 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.

  3. (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.

To view the properties of a push subscription to a merge publication

  1. At the Publisher on the publication database, execute sp_helpmergesubscription. Specify @publication and @subscriber.

  2. At the Publisher, execute sp_helpsubscriberinfo, specifying @subscriber.

To change the properties of a push subscription to a merge publication

  1. 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.

Example (Transact-SQL)

Using Replication Management Objects (RMO)

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.

To view or modify properties of a push subscription to a snapshot or transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransSubscription class.

  3. Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties.

  4. Set the ServerConnection from step 1 for the ConnectionContext property setting.

  5. 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.

  6. (Optional) To change properties, set a new value for one of the TransSubscription properties that can be set, and then call the CommitPropertyChanges method.

  7. (Optional) To view the new settings, call the Refresh method to reload the properties for the subscription.

To view or modify properties of a push subscription to a merge publication

  1. Create a connection to the Subscriber by using the ServerConnection class.

  2. Create an instance of the MergeSubscription class.

  3. Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties.

  4. Set the ServerConnection from step 1 for the ConnectionContext property setting.

  5. 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.

  6. (Optional) To change properties, set a new value for one of the MergeSubscription properties that can be set, and then call the CommitPropertyChanges method.

  7. (Optional) To view the new settings, call the Refresh method to reload the properties for the subscription.

Related content