sp_changesubstatus Replication Stored Procedure

Changes the status of an existing subscriber.

Syntax

sp_changesubstatus [publication [, article [, subscriber [, ]]]]
status [, previous_status]

where

publication

Is the name of the publication. If publication is not specified, all publications are affected.

article

Is the name of the article. It must be unique to the publication. If article is not specified, all articles are affected.

subscriber

Is the name of the subscription server that is to have its status changed. If no subscriber is specified, status is changed for all subscribers to the specified article.

status

Is the subscription status in the syssubscriptions table. Can be:

inactive	Subscriber entry exists without a subscription, which is used for restricted publications.
subscribed	Subscriber is requesting data but is not yet synchronized.
active	Subscriber is synchronized and receiving data.

previous_status

Allows you to change any subscriptions that currently have that status, thus allowing group functions on a specific set of subscriptions. (For example, setting all active subscriptions back to subscribed.) Normally, is NULL.

Remarks

This stored procedure changes the subscriber's status in the syssubscriptions table with the changed status. If required, it updates the article status in the sysarticles table to indicate active or inactive. If required, it sets the replication flag on or off in the sysobjects table for the replicated table.

Example

This example changes the status of the subscriber POODLE to inactive (entry exits without subscription).

sp_changesubstatus mypub1, myart3, POODLE, inactive

Permission

Execute permission defaults to the system administrator and the database owner.

Tables Used

sysobjects, sysarticles, syspublications, syssubscriptions, sysservers

See Also