How to: View and Modify Pull Subscription Properties (Replication Transact-SQL Programming)
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.