sp_addmergesubscription (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Managed Instance
Creates a push or pull merge subscription. This stored procedure is executed at the Publisher on the publication database.
Transact-SQL syntax conventions
Syntax
sp_addmergesubscription
[ @publication = ] N'publication'
[ , [ @subscriber = ] N'subscriber' ]
[ , [ @subscriber_db = ] N'subscriber_db' ]
[ , [ @subscription_type = ] N'subscription_type' ]
[ , [ @subscriber_type = ] N'subscriber_type' ]
[ , [ @subscription_priority = ] subscription_priority ]
[ , [ @sync_type = ] N'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 = ] N'optional_command_line' ]
[ , [ @description = ] N'description' ]
[ , [ @enabled_for_syncmgr = ] N'enabled_for_syncmgr' ]
[ , [ @offloadagent = ] offloadagent ]
[ , [ @offloadserver = ] N'offloadserver' ]
[ , [ @use_interactive_resolver = ] N'use_interactive_resolver' ]
[ , [ @merge_job_name = ] N'merge_job_name' ]
[ , [ @hostname = ] N'hostname' ]
[ ; ]
Arguments
[ @publication = ] N'publication'
The name of the publication. @publication is sysname, with no default. The publication must already exist.
[ @subscriber = ] N'subscriber'
The name of the Subscriber. @subscriber is sysname, with a default of NULL.
[ @subscriber_db = ] N'subscriber_db'
The name of the subscription database. @subscriber_db is sysname, with a default of NULL.
[ @subscription_type = ] N'subscription_type'
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 don't need to use this stored procedure.
[ @subscriber_type = ] N'subscriber_type'
The type of Subscriber. @subscriber_type is nvarchar(15), and can be one of the following values.
| Value | Description |
|---|---|
local (default) |
Subscriber known only to the Publisher. |
global |
Subscriber known to all servers. |
In SQL Server 2005 (9.x) and later versions, local subscriptions are referred to as client subscriptions, and global subscriptions are referred to as server subscriptions.
[ @subscription_priority = ] subscription_priority
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.
[ @sync_type = ] N'sync_type'
The subscription synchronization type. @sync_type is nvarchar(15), with a default of automatic.
- If
automatic, the schema and initial data for published tables are transferred to the Subscriber first. - If
none, the Subscriber is assumed to already have the schema and initial data for published tables. System tables and data are always transferred.
Note
We recommend not specifying a value of none.
[ @frequency_type = ] frequency_type
A value indicating when the Merge Agent runs. @frequency_type is int, and can be one of the following 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 day or days that the Merge Agent runs. @frequency_interval is int, and can be one of the following 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
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
The recurrence factor used by @frequency_type. @frequency_recurrence_factor is int, with a default of NULL.
[ @frequency_subday = ] frequency_subday
The unit for @frequency_subday_interval. @frequency_subday is int, and can be one of the following values.
| Value | Description |
|---|---|
1 |
Once |
2 |
Second |
4 |
Minute |
8 |
Hour |
NULL (default) |
[ @frequency_subday_interval = ] frequency_subday_interval
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
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
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
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
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 = ] N'optional_command_line'
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 = ] N'description'
A brief description of this merge subscription. @description is nvarchar(255), with a default of NULL. This value is displayed by the Replication Monitor in the Friendly Name column, which can be used to sort the subscriptions for a monitored publication.
[ @enabled_for_syncmgr = ] N'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 isn't registered with Synchronization Manager. - If
true, the subscription is registered with Synchronization Manager and can be synchronized without starting SQL Server Management Studio.
[ @offloadagent = ] offloadagent
Specifies that the agent can be activated remotely. @offloadagent is bit, with a default of 0.
This parameter is deprecated and is maintained for backward compatibility of scripts.
[ @offloadserver = ] N'offloadserver'
Specifies the network name of server to be used for remote agent activation. @offloadserver is sysname, with a default of NULL.
[ @use_interactive_resolver = ] N'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 = ] N'merge_job_name'
This parameter is deprecated and can't be set. @merge_job_name is sysname, with a default of NULL.
[ @hostname = ] N'hostname'
Overrides the value returned by HOST_NAME when this function is used in the WHERE clause of a parameterized filter. @hostname is sysname, with a default of NULL.
Important
For performance reasons, we recommend that you not apply functions to column names in parameterized row filter clauses, such as LEFT([MyColumn]) = SUSER_SNAME(). If you use HOST_NAME in a filter clause and override the HOST_NAME value, it might be necessary to convert data types using CONVERT. For more information about best practices for this case, see the section "Overriding the HOST_NAME() Value" in the topic Parameterized Filters - Parameterized Row Filters.
Return code values
0 (success) or 1 (failure).
Remarks
sp_addmergesubscription is used in merge replication.
When sp_addmergesubscription is executed by a member of the sysadmin fixed server role to create a push subscription, the Merge Agent job is implicitly created and runs under the SQL Server Agent service account. We recommend that you execute sp_addmergepushsubscription_agent and specify the credentials of a different, agent-specific Windows account for @job_login and @job_password. For more information, see Replication Agent Security Model.
Examples
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @hostname AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica';
SET @hostname = N'adventure-works\david8'
-- Add a push subscription to a merge publication.
USE [AdventureWorks2022];
EXEC sp_addmergesubscription
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriptionDB,
@subscription_type = N'push',
@hostname = @hostname;
--Add an agent job to synchronize the push subscription.
EXEC sp_addmergepushsubscription_agent
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriptionDB,
@job_login = $(Login),
@job_password = $(Password);
GO
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergesubscription.