Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_addsubscriber (Transact-SQL)

Adds a new Subscriber to a Publisher, enabling it to receive publications. This stored procedure is executed at the Publisher on the publication database for snapshot and transactional publications; and for merge publications using a remote Distributor, this stored procedure is executed at the Distributor.

ms188360.note(en-US,SQL.90).gifImportant:
This stored procedure has been deprecated. You are no longer required to explicitly register a Subscriber at the Publisher.

Topic link icon Transact-SQL Syntax Conventions


sp_addsubscriber [ @subscriber = ] 'subscriber'
    [ , [ @type = ] type ] 
    [ , [ @login = ] 'login' ]
    [ , [ @password = ] 'password' ]
    [ , [ @commit_batch_size = ] commit_batch_size ]
    [ , [ @status_batch_size = ] status_batch_size ]
    [ , [ @flush_frequency = ] flush_frequency ]
    [ , [ @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 ]
    [ , [ @description = ] 'description' ]
    [ , [ @security_mode = ] security_mode ]
    [ , [ @encrypted_password = ] encrypted_password ]
    [ , [ @publisher = ] 'publisher' ]
[ @subscriber=] 'subscriber'

Is the name of the server to be added as a valid Subscriber to the publications on this server. subscriber is sysname, with no default.

[ @type=] type

Is the type of Subscriber. type is tinyint, and can be one of these values.

Value Description

0 (default)

Microsoft SQL Server Subscriber

1

ODBC data source server

2

Microsoft Jet database

3

OLE DB provider

[ @login=] 'login'

Is the login ID for SQL Server Authentication. login is sysname, with a default of NULL.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @password=] 'password'

Is the password for SQL Server Authentication. password is nvarchar(524), with a default of NULL.

ms188360.security(en-US,SQL.90).gifSecurity Note:
Do not use a blank password. Use a strong password.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @commit_batch_size=] commit_batch_size

This parameter has been deprecated and is maintained for backward compatibility of scripts.

ms188360.note(en-US,SQL.90).gifNote:
When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @status_batch_size=] status_batch_size

This parameter has been deprecated and is maintained for backward compatibility of scripts.

ms188360.note(en-US,SQL.90).gifNote:
When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @flush_frequency=] flush_frequency

This parameter has been deprecated and is maintained for backward compatibility of scripts.

ms188360.note(en-US,SQL.90).gifNote:
When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @frequency_type=] frequency_type

Is the frequency with which to schedule the replication agent. frequency_type is int, and can be one of these values.

Value Description

1

One time

2

On demand

4

Daily

8

Weekly

16

Monthly

32

Monthly relative

64 (default)

Autostart

128

Recurring

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[@frequency_interval = ] frequency_interval

Is the value applied to the frequency set by frequency_type. frequency_interval is int, with a default of 1.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @frequency_relative_interval=] frequency_relative_interval

Is the date of the replication agent. 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 (default)

First

2

Second

4

Third

8

Fourth

16

Last

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @frequency_recurrence_factor=] frequency_recurrence_factor

Is the recurrence factor used by frequency_type. frequency_recurrence_factor is int, with a default of 0.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @frequency_subday=] frequency_subday

Is how often to reschedule during the defined period. frequency_subday is int, and can be one of these values.

Value Description

1

Once

2

Second

4 (default)

Minute

8

Hour

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @frequency_subday_interval=] frequency_subday_interval

Is the interval for frequency_subday. frequency_subday_interval is int, with a default of 5.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @active_start_time_of_day=] active_start_time_of_day

Is the time of day when the replication agent is first scheduled, formatted as HHMMSS. active_start_time_of_day is int, with a default of 0.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @active_end_time_of_day=] active_end_time_of_day

Is the time of day when the replication agent stops being scheduled, formatted as HHMMSS. active_end_time_of_day is int, with a default of 235959, which means 11:59:59 P.M. as measured on a 24-hour clock.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @active_start_date=] active_start_date

Is the date when the replication agent is first scheduled, formatted as YYYYMMDD. active_start_date is int, with a default of 0.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @active_end_date=] active_end_date

Is the date when the replication agent stops being scheduled, formatted as YYYYMMDD. active_end_date is int, with a default of 99991231, which means December 31, 9999.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @description=] 'description'

Is a text description of the Subscriber. description is nvarchar(255), with a default of NULL.

[ @security_mode=] security_mode

Is the implemented security mode. security_mode is int, with a default of 1. 0 specifies SQL Server Authentication. 1 specifies Windows Authentication.

ms188360.note(en-US,SQL.90).gifNote:
This parameter has been deprecated and is maintained for backward compatibility of scripts. The property is now specified on a per-subscription basis when executing sp_addsubscription. When a value is specified, it will be used as a default when creating subscriptions at this Subscriber and a warning message will be returned.

[ @encrypted_password=] encrypted_password

This parameter has been deprecated and is provided for backward-compatibility only Setting encrypted_password to any value but 0 will result in an error.

[ @publisher= ] 'publisher'

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

ms188360.note(en-US,SQL.90).gifNote:
publisher should not be used when publishing from a SQL Server Publisher.

0 (success) or 1 (failure)

sp_addsubscriber is used in snapshot replication, transactional replication, and merge replication.

sp_addsubscriber is not required when the Subscriber will only have anonymous subscriptions to merge publications.

sp_addsubscriber writes to the MSsubscriber_info table in the distribution database.

Only members of the sysadmin fixed server role can execute sp_addsubscriber.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.