Export (0) Print
Expand All
Expand Minimize

sp_changesubstatus (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions


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 ]
    [ , [ @skipobjectactivation = ] skipobjectactivation
  [ , [ @distribution_job_name= ] 'distribution_job_name' ]
    [ , [ @publisher = ] 'publisher' ]

[ @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 is synchronized and is 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.

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

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= ] remote_agent_activation
NoteNote

Remote agent activation has been deprecated and is no longer supported. This parameter is supported only to maintain backward compatibility of scripts. Setting remote_agent_activation to a value other than 0 generates an error.

[ @offloadserver= ] 'remote_agent_server_name'
NoteNote

Remote agent activation has been deprecated and is no longer supported. This parameter is supported only to maintain backward compatibility of scripts. Setting remote_agent_server_name to any non-NULL value generates an error.

[ @dts_package_name= ] 'dts_package_name'

Specifies the name of the Data Transformation Services (DTS) package. dts_package_name is a 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= ] 'dts_package_password'

Specifies the password on the package. dts_package_password is sysname with a default of NULL, which specifies that the password property is to be left unchanged.

NoteNote

A DTS package must have a password.

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

[ @skipobjectactivation= ] skipobjectactivation

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @distribution_job_name= ] 'distribution_job_name'

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

[ @publisher= ] 'publisher'

Specifies a non-Microsoft SQL Server Publisher. publisher is sysname, with a default of NULL.

NoteNote

publisher should not be used when changing article properties on a SQL Server Publisher.

0 (success) or 1 (failure)

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.

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft