Export (0) Print
Expand All
Expand Minimize

sp_addmergesubscription

SQL Server 2000

Creates a push or pull merge subscription. This stored procedure is executed at the Publisher on the publication database.

Syntax

sp_addmergesubscription [ @publication = ] 'publication'
    [ , [ @subscriber = ] 'subscriber' ]
    [ , [ @subscriber_db = ] 'subscriber_db' ]
    [ , [ @subscription_type = ] 'subscription_type' ]
    [ , [ @subscriber_type = ] 'subscriber_type' ]
    [ , [ @subscription_priority = ] subscription_priority ]
    
[ , [ @sync_type = ] 'sync_type' ]
    [ , [ @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' ]
    [ , [ @description = ] 'description' ]
    [ , [ @enabled_for_syncmgr = ] 'enabled_for_syncmgr' ]
    [ , [ @offloadagent = ] remote_agent_activation]
    [ , [ @offloadserver = ] 'remote_agent_server_name' ]
    [ , [ @use_interactive_resolver = ] 'use_interactive_resolver' ]
    [ , [ @merge_job_name = ] 'merge_job_name' ]

Arguments

[@publication = ] 'publication'

Is the name of the publication. publication is sysname, with no default. The publication must already exist.

[@subscriber = ] 'subscriber'

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

[@subscriber_db = ] 'subscriber_db'

Is the name of the subscription database. subscriber_db is sysname, with a default of NULL.

[@subscription_type = ] 'subscription_type'

Is the type of subscription. subscription_type is nvarchar(15), with a default of PUSH. If push, a push subscription is added and the Merge Agent is added at the Distributor. If pull, a pull subscription is added without adding a Merge Agent at the Distributor.

Note  Anonymous subscriptions do not need to use this stored procedure.

[@subscriber_type = ] 'subscriber_type'

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

Value Description
local (default) Subscriber known only to the Publisher.
global Subscriber known to all servers.

[@subscription_priority = ] subscription_priority

Is a number indicating the priority for the subscription. subscription_priority is real, with a default of NULL. For local and anonymous subscriptions, the priority is 0.0. For global subscriptions, the priority must be less than 100.0. For more information, see Subscriber Types and Conflicts.

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

[@frequency_type = ] frequency_type

Is a value indicating when the Merge Agent will run. frequency_type is int, and can be one of these values.

Value Description
1 Once
4 Daily
8 Weekly
10 Monthly
20 Monthly, relative to the frequency interval
40 When SQL Server Agent starts
NULL (default)  

[@frequency_interval = ] frequency_interval

The days that the Merge Agent runs. frequency_interval is int, and can be one of these values.

Value Description
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
8 Day
9 Weekdays
10 Weekend days
NULL (default)  

[@frequency_relative_interval = ] frequency_relative_interval

Is the scheduled merge occurrence of the frequency interval in each month. 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 the units for freq_subday_interval. frequency_subday is int, and can be one of these values.

Value Description
1 Once
2 Second
4 Minute
8 Hour
NULL (default)  

[@frequency_subday_interval = ] frequency_subday_interval

Is the frequency for frequency_subday to occur between each merge. 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 Merge 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 Merge 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 Merge 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 Merge 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. This parameter is used to add a command that captures the output and saves it to a file or to specify a configuration file or attribute.

[@description = ] 'description'

Is a brief description of this merge subscription. description is nvarchar(255), with a default of NULL.

[@enabled_for_syncmgr = ] 'enabled_for_syncmgr'

Specifies if 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 Synchronization Manager. If true, the subscription is registered with 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_activationis bit with a default of 0. 0 specifies the agent cannot be activated remotely. 1 specifies the agent will be activated remotely, and on the remote computer specified by remote_agent_server_name.

[@offloadserver = ] 'remote_agent_server_name'

Specifies the network name of server to be used for remote agent activation. remote_agent_server_name is sysname, with a default of NULL.

[@use_interactive_resolver = ] 'use_interactive_resolver'

Allows conflicts to be resolved interactively for all articles that allow interactive resolution. use_interactive_resolver is nvarchar(5), with a default of FALSE.

[@merge_job_name = ] 'merge_job_name'

For internal only use.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addmergesubscription is used in merge replication.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergesubscription.

See Also

Interactive Resolver

sp_changemergesubscription

sp_dropmergesubscription

sp_helpmergesubscription

System Stored Procedures

Show:
© 2014 Microsoft