How to: View and Modify Push Subscription Properties (Replication Transact-SQL Programming)

  • Article

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.

See Also

Tasks

How to: View and Modify Pull Subscription Properties (Replication Transact-SQL Programming)

Other Resources

Subscribing to Publications

Help and Information

Getting SQL Server 2005 Assistance