sp_changesubstatus (T-SQL)

Changes the status of an existing Subscriber.

Syntax

sp_changesubstatus [[@publication =] 'publication'] [,[@article =] 'article']
    [,[@subscriber =] 'subscriber'] {,[@status =] 'status'}
    [,[@previous_status =] 'previous_status']
    [,[@destination_db =] 'destination_db']
    [,[@frequency_type =] frequency_type]
    [,[@frequency_interval =] frequency_interval]
    [,[@frequency_relative_interval =] frequency_relative_interval]
    [,[@frequency_recurrence_factor =] frequency_recurrence_factor]
    [,[@frequency_subday =] frequency_subday]
    [,[@frequency_subday_interval =] frequency_subday_interval]
    [,[@active_start_time_of_day =] active_start_time_of_day]
    [,[@active_end_time_of_day =] active_end_time_of_day]
    [,[@active_start_date =] active_start_date]
    [,[@active_end_date =] active_end_date]
    [,[@optional_command_line =] 'optional_command_line']
    [,[@distribution_jobid =] distribution_jobid]
    [,[@from_auto_sync =] from_auto_sync]
    [,[@ignore_distributor =] ignore_distributor]

Arguments

[@publication =] 'publication'

Is the name of the publication. publication is sysname, with a default of %. If publication is not specified, all publications are affected.

[@article =] 'article'

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

[@subscriber =] 'subscriber'

Is the name of the Subscriber to change the status of subscriber is sysname, with a default of %. If subscriber is not specified, status is changed for all Subscribers to the specified article.

[@status =] 'status'

Is the subscription status in the syssubscriptions table. status is sysname, with no default, and can be one of these values.

 

Value	Description
active	Subscriber synchronized and receiving data.
inactive	Subscriber entry exists without a subscription.
subscribed	Subscriber is requesting data but is not yet synchronized.

[@previous_status =] 'previous_status'

Is the previous status for the subscription. previous_status is sysname, with a default of NULL. This parameter 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).

[@destination_db =] 'destination_db'

Is the name of the destination database. destination_db is sysname, with a default of %.

[@frequency_type =] frequency_type

Is the frequency with which to schedule the distribution task. frequency_type is int, with a default of NULL. If no value is provided for frequency_type, sp_changesubstatus uses the frequency_type value used by sp_addsubscriber.

[@frequency_interval =] frequency_interval

Is the value to apply to the frequency set by frequency_type. frequency_interval is int, with a default of NULL.

[@frequency_relative_interval =] frequency_relative_interval

Is the date of the distribution task. This parameter is used when frequency_type is set to 32 (monthly relative). frequency_relative interval is int, and can be one of these values.

 

Value	Description
1	First
2	Second
4	Third
8	Fourth
16	Last
NULL (default)

[@frequency_recurrence_factor =] frequency_recurrence_factor

Is the recurrence factor used by frequency_type. frequency_recurrence_factor is int, with a default of NULL.

[@frequency_subday =] frequency_subday

Is how often, in minutes, to reschedule during the defined period. frequency_subday is int, and can be one of these values.

 

Value	Description
1	Once
2	Second
4	Minute
8	Hour
NULL (default)

[@frequency_subday_interval =] frequency_subday_interval

Is the interval for frequency_subday. frequency_subday_interval is int, with a default of NULL.

[@active_start_time_of_day =] active_start_time_of_day

Is the time of day when the distribution task is first scheduled, formatted as HHMMSS. active_start_time_of_day is int, with a default of NULL.

[@active_end_time_of_day =] active_end_time_of_day

Is the time of day when the distribution task stops being scheduled, formatted as HHMMSS. active_end_time_of_day is int, with a default of NULL.

[@active_start_date =] active_start_date

Is the date when the distribution task is first scheduled, formatted as YYYYMMDD. active_start_date is int, with a default of NULL.

[@active_end_date =] active_end_date

Is the date when the distribution task stops being scheduled, formatted as YYYYMMDD. active_end_date is int, with a default of NULL.

[@optional_command_line =] 'optional_command_line'

Is an optional command prompt. optional_command_line is nvarchar(4000), with a default of NULL.

[@distribution_jobid =] distribution_jobid

Is the job ID of the Distribution Agent at the Distributor for the subscription when changing the subscription status from inactive to active. In other cases, it is not defined. If more that one Distribution Agent is involved in a single call to this stored procedure, the result is not defined. distribution_jobid is binary(16), with a default of NULL.

[@from_auto_sync =] from_auto_sync

Reserved for internal use only.

[@ignore_distributor =] ignore_distributor

Reserved for internal use only.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_changesubstatus is used in snapshot and transactional replication.

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

Permissions

Only members of the sysadmin fixed server role, db_owner fixed database role, or the creator of the subscription can execute sp_changesubstatus.

See Also