sp_addsubscription
Adds a subscription to a publication and sets the Subscriber status. This stored procedure is executed at the Publisher on the publication database.
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' ]
[ , [ @offloadagent = ] remote_agent_activation]
[ , [ @offloadserver = ] 'remote_agent_server_name' ]
[ , [ @dts_package_name = ] 'dts_package_name' ]
[ , [ @dts_package_password = ] 'dts_package_password' ]
[ , [ @dts_package_location = ] 'dts_package_location' ]
[ , [ @distribution_job_name = ] 'distribution_job_name' ]
Arguments
[@publication = ] 'publication'
Is the name of the publication. publication is sysname, with no default.
[@article = ] 'article'
Is the article to which the publication is subscribed. 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), with a default of automatic. Can be automatic or none. If automatic, the schema and initial data for published tables are transferred to the Subscriber first. If none, it is assumed the Subscriber already has the schema and initial data for published tables. System tables and data are always transferred.
[@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. |
| NULL (default) |
[@subscription_type = ] 'subscription_type'
Is the type of subscription. subscription_type is nvarchar(4), with a default of push. Can be push or pull. The Distribution Agents of push subscriptions reside at the Distributor, and the Distribution Agents of pull subscriptions reside at the Subscriber. subscription_type can be pull to create a named pull subscription that is known to the Publisher. For more information, see Subscribing to Publications.
Note Anonymous subscriptions do not need to use this stored procedure.
[@update_mode = ] 'update_mode'
Is the type of update. update_mode is nvarchar(30), and can be one of these values.
| Value | Description |
|---|---|
| read only (default) | The subscription is read-only. The changes at the Subscriber will not be sent to the Publisher. |
| sync tran | Enables support for immediate updating subscriptions. |
| queued tran | Enables the subscription for queued updating. Data modifications can be made at the Subscriber, stored in a queue, and then propagated to the Publisher. |
| failover | Enables the subscription for immediate updating with queued updating as a failover. Data modifications can be made at the Subscriber and propagated to the Publisher immediately. If the Publisher and Subscriber are not connected, data modifications made at the Subscriber can be stored in a queue until the Subscriber and Publisher are reconnected. |
Note that the values synctran and queued tran are not allowed if the publication being subscribed to allows DTS.
[@loopback_detection = ] 'loopback_detection'
Specifies if the Distribution Agent sends transactions that 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 only if the subscription update_mode is synctran and the article table has a published timestamp column. |
| false | Distribution Agent sends transactions that 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'
For internal use only.
[@enabled_for_syncmgr = ] 'enabled_for_syncmgr'
Is whether the subscription can be synchronized through Microsoft Windows Synchronization Manager. enabled_for_syncmgr is nvarchar(5), with a default of FALSE. If false, the subscription is not registered with Windows Synchronization Manager. If true, the subscription is registered with Windows Synchronization Manager and can be synchronized without starting SQL Server Enterprise Manager.
[@offloadagent = ] 'remote_agent_activation'
Specifies that the agent can be activated remotely. remote_agent_activation is bit with a default of 0. 0 specifies the agent cannot be activated remotely. 1 specifies the agent can be activated remotely.
[@offloadserver = ] 'remote_agent_server_name'
Specifies the network name of server to be used for remote activation. remote_agent_server_name is sysname, with a default of NULL.
[@dts_package_name = ] 'dts_package_name'
Specifies the name of the DTS package. dts_package_name is a sysname with a default of NULL. For example, to specify a package of DTSPub_Package, the parameter would be @dts_package_name = N'DTSPub_Package'. This parameter is available for push subscriptions. To add DTS package information to a pull subscription, use sp_addpullsubscription_agent.
[@dts_package_password = ] 'dts_package_password'
Specifies the password on the package, if there is one. dts_package_password is sysname with a default of NULL, which means a password in not on the package.
[@dts_package_location = ] 'dts_package_location'
Specifies the package location. dts_package_location is a nvarchar(12), with a default of DISTRIBUTOR. The location of the package can be distributor or subscriber.
[@distribution_job_name = ] 'distribution_job_name'
For internal use only.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_addsubscription is used in snapshot replication and transactional replication.
sp_addsubscription prevents ODBC and 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.
In addition, if a publication has the allow_queued_tran option set to true (which enables queuing of changes at the Subscriber until they can be applied at the Publisher), the timestamp column in an article will be scripted out as timestamp, and changes on that column will be sent to the Subscriber. The Subscriber will generate and update the timestamp column value. For an ODBC/OLE DB Subscriber, sp_addsubscription will fail if an attempt is made to subscribe to a publication that has allow_queued_tran set to true and articles with timestamp columns in it.
If a subscription does not use a DTS package, it cannot subscribe to a publication that is set to allow_transformable_subscriptions. If the table from the publication needs to be replicated to both a DTS subscription and non-DTS subscription, two separate publications will have to be created: one for each type of subscription.
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.