sp_addsubscription (T-SQL)

Adds a subscription to an article and sets the Subscriber’s status.

Syntax

sp_addsubscription [@publication =] 'publication'
    [,[@article =] 'article'] [,[@subscriber =] 'subscriber']
    [,[@destination_db =] 'destination_db', [@sync_type =] 'sync_type']
    [,[@status =] 'status' [@subscription_type =] 'subscription_type']
    [,[@update_mode =] 'update_mode']
    [,[@loopback_detection =] 'loopback_detection']
    [,[@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']
    [,[@reserved =] 'reserved']
    [,[@enabled_for_syncmgr =] 'enabled_for_syncmgr']

Arguments

[@publication =] 'publication'

Is the name of the publication. publication is sysname, with no default.

[@article =] 'article'

Is the article that is subscribed to. article is sysname, with a default of all. The article name must be unique within the publication. If all or not supplied, a subscription is added to all articles in that publication.

[@subscriber =] 'subscriber'

Is the name of the Subscriber. subscriber is sysname, with a default of NULL.

[@destination_db =] 'destination_db'

Is the name of the destination database in which to place replicated data. destination_db is sysname, with a default of NULL, and uses the same name as the publication database.

[@sync_type =] 'sync_type'

Is the subscription synchronization type. sync_type is nvarchar(15), and can be one of these values.

 

Value	Description
automatic (default)	Synchronization is applied to the Subscriber through the distribution process.
manual	A synchronization file is automatically produced, but it must be manually applied to the Subscriber.
none	No initial synchronization.

[@status =] 'status'

Is the subscription status. status is sysname, and can be one of these values.

 

Value	Description
active	If sync_type is none, the default for status is active. To enable a Subscriber to see articles in a restricted publication article, a placeholder subscription must be created with inactive status. If sync_type is automatic, status cannot be set to active.
subscribed	If sync_type is other than none, the default for status is subscribed.

[@subscription_type =] 'subscription_type'

Is the type of subscription. subscription_type is nvarchar(4), with a default of push. Can be push or pull.

---

Note Anonymous subscriptions do not need to use this stored procedure. The Distribution Agents of push subscriptions reside at the Distributor, and the Distribution Agents of pull subscriptions reside at the Subscriber.

---

[@update_mode =] 'update_mode'

Is the type of update. update_mode is nvarchar(15), and can be one of these values.

 

Value	Description
read-only (default)	Disables support for immediate-updating Subscribers.
synctran	Enables support for immediate-updating Subscribers.

[@loopback_detection =] 'loopback_detection'

Is whether the Distribution Agent sends transactions originated at the Subscriber back to the Subscriber. loopback_detection is nvarchar(5), and can be one of these values.

 

Value	Description
true	Distribution Agent does not send transactions originated at the Subscriber back to the Subscriber. The value can be set to true if and only if the subscription update_mode is synctran and the article table has a published timestamp column.
false	Distribution Agent sends transactions originated at the Subscriber back to the Subscriber.
NULL (default)

[@frequency_type =] frequency_type

Is the frequency with which to schedule the Distribution Agent. frequency_type is int, with a default of NULL. If no value is specified, sp_addsubscription uses the value specified in sp_addsubscriber.

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

[@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 Agent 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 Agent 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 Agent 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 Agent stops being scheduled, formatted as YYYYMMDD. active_end_date is int, with a default of NULL.

[@optional_command_line =] 'optional_command_line'

Is the optional command prompt to execute. optional_command_line is nvarchar(4000), with a default of NULL.

[@reserved =] 'reserved'

Is reserved. reserved is nvarchar(10), with a default of NULL.

[@enabled_for_syncmgr =] 'enabled_for_syncmgr'

Is whether the subscription can be synchronized through the Microsoft Synchronization Manager. enabled_for_syncmgr is nvarchar(5), with a default of false. If false, the subscription is not registered with Synchronization Manager. If true, the subscription is registered with Synchronization Manager and can be synchronized without launching SQL Server Enterprise Manager.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addsubscription is used in snapshot and transactional replication.

sp_addsubscription prevents ODBC/OLE DB Subscribers access to publications that:

• Were created with the native @sync_method in the call to sp_addpublication.
• Contain articles that were added to the publication with an sp_addarticle stored procedure that had a pre_creation_cmd parameter value of 3 (truncate).
• Attempt to set @update_mode to synchtran.
• Have an article configured to use parameterized statements.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addsubscription. For pull subscriptions, users with logins in the publication access list can execute sp_addsubscription.

See Also