sp_addsubscriber Replication Stored Procedure

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

Adds a new subscription server and sets up a trusted remote login mapping from SA of the subscriber to the repl_subscriber login ID on the publisher (unless system administrator on the subscriber is already mapped to system administrator on the publisher).

Syntax

sp_addsubscriber 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

Specifies the name of the server to be added as a valid subscriber to this server's publications.

type

Specifies the type of subscription server: 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. The default is 20.

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. The default is 0.

frequency_type

Specifies the frequency with which to schedule the distribution task. The default is 4 (daily). The value can be:
1One Time
2On Demand
4Daily
8Weekly
16Monthly
32Monthly Relative
64Autostart
124Recurring

frequency_interval

Is the value to apply to the frequency set by frequency_type. The default is 1.

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
2Second
4Third
8Fourth
16Last

frequency_recurrence_factor

Is the recurrence factor used by frequency_type. The default is 0.

frequency_subday

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

frequency_subday_interval

Is the interval for frequency_subday. The default is 5.

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

active_start_date

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

active_end_date

Specifies the date when the distribution task will stop being scheduled. Formatted as YYYYMMDD. The default is 99991231.

Remarks

This stored procedure writes to the MSsubscriber_info table in the distribution database.

Example

This example sets up BLOODHOUND as a subscription server.

sp_addsubscriber BLOODHOUND

Permission

Execute permission defaults to the system administrator.

Tables Used

sysservers, sysremotelogins

See Also