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

sp_addsubscription sp_helpsubscription
sp_changesubscription sp_subscribe
sp_dropsubscription sp_unsubscribe
sp_helpdistributor