sp_changesubstatus

Changes the status of an existing Subscriber. This stored procedure is executed at the Publisher on the publication database.

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 ]
    [ , [ @offloadagent = ] remote_agent_activation ]
    [ , [ @offloadserver = ] 'remote_agent_server_name' ]
    [ , [ @dts_package_name = ] 'dts_package_name' ]
    [ , [ @dts_package_password = ] 'dts_package_password' ]
    [ , [ @dts_package_location = ] dts_package_location ]
    [ , [ @schemastabilityonly = ] schema_stability_only ]
    [ , [ @distribution_job_name = ] 'distribution_job_name' ]

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.

[@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.

[@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.

[@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 than 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

For internal use only.

[@ignore_distributor =] ignore_distributor

For internal use only.

[@offloadagent = ] remote_agent_activation

Specifies that the agent can be activated remotely. remote_agent_activationt is bit, with a default of 0. 0 specifies the agent cannot be activated remotely. 1 specifies the agent can be activated remotely, and on the remote computer specified by remote_agent_server_name.

[@offloadserver = ] 'remote_agent_server_name'

Specifies the network name of server to be used for remote activation. remote_agent_server_name is sysname, with a default of NULL.

[@dts_package_name = ] 'dts_package_name'

Specifies the name of the DTS package. dts_package_name is a sysname, with a default of NULL. For example, to specify a package of DTSPub_Package, the parameter would be @dts_package_name = N'DTSPub_Package'.

[@dts_package_password = ] 'dts_package_password'

Specifies the password on the package, if there is one. dts_package_password is  sysname, with a default of NULL, which means that there is not a password on the package.

[@dts_package_location = ] dts_package_location

Specifies the package location. dts_package_location is an 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.

[@schemastabilityonly = ] schema_stability_only

For internal use only.

[@distribution_job_name = ] 'distribution_job_name'

Is the name of the distribution job. distribution_job_name is sysname, with a default of NULL.

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.

See Also

sp_addsubscription

sp_dropsubscription

sp_helpdistributor

sp_helpsubscription

System Stored Procedures