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.

Important

This stored procedure has been deprecated. You are no longer required to explicitly register a Subscriber at the Publisher.

Transact-SQL Syntax Conventions

Syntax

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' ]

Arguments

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

  Note

  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.

  Security Note
  Do not use a blank password. Use a strong password.

  Note

  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.

  Note

  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.

  Note

  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.

  Note

  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

  Note

  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.

  Note

  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

  Note

  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.

  Note

  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

  Note

  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.

  Note

  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.

  Note

  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_dayis int, with a default of 235959, which means 11:59:59 P.M. as measured on a 24-hour clock.

  Note

  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.

  Note

  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.

  Note

  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.

  Note

  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.

  Note

  publisher should not be used when publishing from a SQL Server Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Permissions

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

See Also

Reference

sp_changesubscriber (Transact-SQL)

sp_dropsubscriber (Transact-SQL)

sp_helpsubscriberinfo (Transact-SQL)

Concepts

Create a Push Subscription

Create a Pull Subscription