sp_changesubscriber Replication Stored Procedure

For SQL Server 6.5 information, see sp_changesubscriber in What's New for SQL Server 6.5.

Changes the options for a subscription server. Any distribution task for the publisher's subscribers will be updated.

Syntax

sp_changesubscriber subscriber [, type] [, login] [, password]
    [, commit_batch_size] [, status_batch_size] [, flush_frequency]
    [, frequency_type] [, frequency_interval] [, frequency_relative_interval]
    [, frequency_recurrence_factor] [, frequency_subday]
    [, frequency_subday_interval] [, active_start_time_of_day]
    [, active_end_time_of_day] [, active_start_date] [, active_end_date]

where

subscriber

Is the name of the subscription server that will have its options changed.

type

Specifies the subscription server type; 0 indicates a SQL Server 6.0 subscription server; 1 indicates a non-SQL Server 6.0 or other ODBC data source server. The default is 0.

login

Is reserved for future use.

password

Is reserved for future use.

commit_batch_size

Specifies the maximum number of transactions that will be sent to the subscription server as a single unit.

status_batch_size

Is reserved for future use.

flush_frequency

Specifies the frequency with which transactions will be removed from the distribution database tables after they have been successfully distributed to the subscriber's database.

frequency_type

Specifies the frequency with which to schedule the distribution task:
1One Time
2On Demand
4Daily
8Weekly
16Monthly
32Monthly Relative
64Autostart
128Recurring

frequency_interval

Specifies the interval for frequency_type.

frequency_relative_interval

Specifies the date of the distribution task. Used when frequency_type is set to 32 (monthly relative). The default is 1. The value can be:

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

frequency_recurrence_factor

Specifies how often the distribution task should re-occur during the defined frequency_type.

frequency_subday

Specifies how often to reschedule during the defined period:
1Once (default)
2Second
4Minute
8Hour

frequency_subday_interval

Specifies the interval for frequency_subday.

active_start_time_of_day

Specifies when the distribution task will first be scheduled. Formatted as HHMMSS. The default is 0.

active_end_time_of_day

Specifies when the distribution task will stop being scheduled. Formatted as HHMMSS. The default is 0.

active_start_date

Specifies the date when the distribution task will be scheduled initially. Formatted as YYYYMMDD. The default is 0.

active_end_date

Specifies the date when the distribution task will stop being scheduled. Formatted as YYYYMMDD. Default is 0.

Remarks

Only the system administrator can change a subscription server. This stored procedure writes to the MSsubscriber_info table in the distribution database.

Example

This example changes the BLOODHOUND subscriber's frequency from daily (the default) to weekly.

sp_changesubscriber BLOODHOUND, @frequency_type = 8

Permission

Execute permission defaults to the system administrator.

Table Used

sysservers

See Also