sp_changesubstatus (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Changes the status of an existing Subscriber. This stored procedure is executed at the Publisher on the publication database.
Transact-SQL syntax conventions
Syntax
sp_changesubstatus
[ [ @publication = ] N'publication' ]
[ , [ @article = ] N'article' ]
[ , [ @subscriber = ] N'subscriber' ]
, [ @status = ] N'status'
[ , [ @previous_status = ] N'previous_status' ]
[ , [ @destination_db = ] N'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 = ] N'optional_command_line' ]
[ , [ @distribution_jobid = ] distribution_jobid OUTPUT ]
[ , [ @from_auto_sync = ] from_auto_sync ]
[ , [ @ignore_distributor = ] ignore_distributor ]
[ , [ @offloadagent = ] offloadagent ]
[ , [ @offloadserver = ] N'offloadserver' ]
[ , [ @dts_package_name = ] N'dts_package_name' ]
[ , [ @dts_package_password = ] N'dts_package_password' ]
[ , [ @dts_package_location = ] dts_package_location ]
[ , [ @skipobjectactivation = ] skipobjectactivation ]
[ , [ @distribution_job_name = ] N'distribution_job_name' ]
[ , [ @publisher = ] N'publisher' ]
[ , [ @ignore_distributor_failure = ] ignore_distributor_failure ]
[ ; ]
Arguments
[ @publication = ] N'publication'
The name of the publication. @publication is sysname, with a default of %. If @publication isn't specified, all publications are affected.
[ @article = ] N'article'
The name of the article. @article is sysname, with a default of %. It must be unique to the publication. If @article isn't specified, all articles are affected.
[ @subscriber = ] N'subscriber'
The name of the Subscriber to change the status of. @subscriber is sysname, with a default of %. If @subscriber isn't specified, status is changed for all Subscribers to the specified article.
[ @status = ] N'status'
The subscription status in the syssubscriptions table. @status is sysname, and can be one of these values.
| Value | Description |
|---|---|
active |
Subscriber is synchronized and is receiving data. |
inactive |
Subscriber entry exists without a subscription. |
subscribed |
Subscriber is requesting data, but isn't yet synchronized. |
[ @previous_status = ] N'previous_status'
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 = ] N'destination_db'
The name of the destination database. @destination_db is sysname, with a default of %.
[ @frequency_type = ] frequency_type
Specifies the frequency with which to schedule the distribution task. @frequency_type is int, with a default of NULL.
[ @frequency_interval = ] frequency_interval
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
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
The recurrence factor used by @frequency_type. @frequency_recurrence_factor is int, with a default of NULL.
[ @frequency_subday = ] frequency_subday
Specifies 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
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
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
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
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
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 = ] N'optional_command_line'
An optional command prompt. @optional_command_line is nvarchar(4000), with a default of NULL.
[ @distribution_jobid = ] distribution_jobid OUTPUT
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 isn't defined. If more than one Distribution Agent is involved in a single call to this stored procedure, the result isn't defined. @distribution_jobid is an OUTPUT parameter of type binary(16).
[ @from_auto_sync = ] from_auto_sync
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @ignore_distributor = ] ignore_distributor
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @offloadagent = ] offloadagent
This parameter is deprecated and is maintained for backward compatibility of scripts. Setting @offloadagent to a value other than 0 generates an error.
[ @offloadserver = ] N'offloadserver'
This parameter is deprecated and is maintained for backward compatibility of scripts. Setting @offloadserver to any non-NULL value generates an error.
[ @dts_package_name = ] N'dts_package_name'
Specifies the name of the Data Transformation Services (DTS) package. @dts_package_name is sysname, with a default of NULL. For example, for a package named DTSPub_Package you would specify @dts_package_name = N'DTSPub_Package'.
[ @dts_package_password = ] N'dts_package_password'
Specifies the password on the package. @dts_package_password is nvarchar(524), with a default of NULL, which specifies that the password property is to be left unchanged.
Note
A DTS package must have a password.
[ @dts_package_location = ] dts_package_location
Specifies the package location. @dts_package_location is int, with a default of 0.
- If
0, the package location is at the Distributor. - If
1, the package location is at the Subscriber.
The location of the package can be distributor or subscriber.
[ @skipobjectactivation = ] skipobjectactivation
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @distribution_job_name = ] N'distribution_job_name'
The name of the distribution job. @distribution_job_name is sysname, with a default of NULL.
[ @publisher = ] N'publisher'
Specifies a non-SQL Server Publisher. @publisher is sysname, with a default of NULL.
@publisher shouldn't be used when changing article properties on a SQL Server Publisher.
[ @ignore_distributor_failure = ] ignore_distributor_failure
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Return code values
0 (success) or 1 (failure).
Remarks
sp_changesubstatus is used in snapshot replication and transactional replication.
sp_changesubstatus changes the status of the Subscriber 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.