Upgrade Replication Scripts (Replication Transact-SQL Programming)

Applies to: SQL Server Azure SQL Managed Instance

Transact-SQL script files can be used to programmatically configure a replication topology. For more information, see Replication System Stored Procedures Concepts.

Important

Although you are not required to upgrade scripts that are executed by members of the sysadmin role, we recommend that you modify existing scripts as described in this topic. Specify an account that has minimum permissions for each replication agent as described in the "Permissions Required By Agents" section of the topic Replication Agent Security Model.

These security improvements, which enable more control over permissions by allowing you to explicitly specify the Microsoft Windows accounts under which replication agent jobs are executed, affect the following stored procedures in existing scripts:

• sp_addpublication_snapshot:

  You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpublication_snapshot (Transact-SQL) to create the job under which the Snapshot Agent runs at the Distributor.

• sp_addpushsubscription_agent:

  You should now execute sp_addpushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Distribution Agent job runs at the Distributor. In versions of SQL Server before SQL Server 2005 (9.x), this was done automatically when a push subscription was created.

• sp_addmergepushsubscription_agent:

  You should now execute sp_addmergepushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Merge Agent job runs at the Distributor. In versions of SQL Server before SQL Server 2005 (9.x), this was done automatically when a push subscription was created.

• sp_addpullsubscription_agent:

  You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpullsubscription_agent (Transact-SQL) to create the job under which the Distribution Agent runs at the Subscriber.

• sp_addmergepullsubscription_agent:

  You should now supply the Windows credentials as @job_login and @job_password when executing sp_addmergepullsubscription_agent (Transact-SQL) to create the job under which the Merge Agent runs at the Subscriber.

• sp_addlogreader_agent:

  You should now execute sp_addlogreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Log Reader Agent runs at the Distributor. In versions of SQL Server before SQL Server 2005 (9.x), this was done automatically when a transactional publication was created.

• sp_addqreader_agent:

  You should now execute sp_addqreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Queue Reader Agent runs at the Distributor. In versions of SQL Server before SQL Server 2005 (9.x), this was done automatically when a transactional publication that supported queued updating was created.

In the security model introduced in SQL Server 2005 (9.x), replication agents always make connections to the local instance of SQL Server with Windows Authentication using the credentials supplied in @job_name and @job_password. For information about the requirements of Windows accounts used when running replication agent jobs, see Replication Agent Security Model.

Important

When possible, prompt users to enter security credentials at runtime. If you store credentials in a script file, ensure that the file itself is secured.

To upgrade scripts that configure a snapshot or transactional publication

1. In the existing script, before sp_addpublication (Transact-SQL), execute sp_addlogreader_agent (Transact-SQL) at the Publisher on the publication database. Specify the Windows credentials under which the Log Reader Agent runs for @job_name and @job_password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Log Reader Agent job for the publication database.

   Note

   This step is only for transactional publications and is not required for snapshot publications.

2. (Optional) Before sp_addpublication (Transact-SQL), execute sp_addqreader_agent (Transact-SQL) at the Distributor on the distribution database. Specify the Windows credentials under which the Queue Reader Agent runs for @job_name and @job_password. This creates a Queue Reader Agent job for the Distributor.

   Note

   This step is only required for transactional publications that support queued updating subscribers.

3. (Optional) Update the execution of sp_addpublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

4. After sp_addpublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

5. (Optional) Update the execution of sp_addarticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

To upgrade scripts that add subscriptions to a snapshot or transactional publication

1. After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Distribution Agent job to synchronize the subscription. The stored procedure that you use will depend on the type of subscription.

   • For a pull subscription, update the execution of sp_addpullsubscription_agent (Transact-SQL) to supply the Windows credentials under which the Distribution Agent runs at the Subscriber for @job_name and @job_password. This is done after the execution of sp_addpullsubscription. For more information, see Create a Pull Subscription.

   • For a push subscription, execute sp_addpushsubscription_agent (Transact-SQL) at the Publisher. Specify @subscriber, @subscriber_db, @publication, Windows credentials under which the Distribution Agent runs at the Distributor for @job_name and @job_password, and a schedule for this agent job. For more information, see Specify Synchronization Schedules. This is done after the execution of sp_addsubscription. For more information, see Create a Push Subscription.

To upgrade scripts that configure a merge publication

1. (Optional) In the existing script, update the execution of sp_addmergepublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

2. After sp_addmergepublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_modeand the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

3. (Optional) Update the execution of sp_addmergearticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

To upgrade scripts that add subscriptions to a merge publication

1. After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Merge Agent job to synchronize the subscription. The stored procedure that you use will depend on the type of subscription.

   • For a pull subscription, update the execution of sp_addmergepullsubscription_agent (Transact-SQL) to supply the Windows credentials under which the Merge Agent runs at the Subscriber for @job_name and @job_password. This is done after the execution of sp_addmergepullsubscription. For more information, see Create a Pull Subscription.

   • For a push subscription, execute sp_addmergepushsubscription_agent (Transact-SQL) at the Publisher. Specify @subscriber, @subscriber_db, @publication, the Windows credentials under which the Merge Agent at the Distributor runs for @job_name and @job_password, and a schedule for this agent job. For more information, see Specify Synchronization Schedules. This is done after the execution of sp_addmergesubscription. For more information, see Create a Push Subscription.

Examples

A. SQL Server 2000 script to create a transactional publication

The following is an example of a SQL Server 2000 (8.x) script that creates a transactional publication for the Product table. This publication supports immediate updating with queued updating as failover. Default parameters have been removed for readability.

USE [Northwind]
GO

DECLARE @publication AS sysname
DECLARE @publicationDB AS sysname
DECLARE @article AS sysname
SET @publication = N'NwdProductTran'
SET @publicationDB = N'Northwind'
SET @article = N'Products'

-- Enable the replication database.
EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'publish', 
    @value = N'true'

-- Add the transactional publication.
EXEC sp_addpublication 
    @publication = @publication, 
    @sync_method = N'native', 
    @status = N'active', 
    @repl_freq = N'continuous', 
    @description = N'Transactional publication of Northwind.', 
    @allow_push = N'true', 
    @allow_pull = N'true', 
    @allow_sync_tran = N'true', 
    @autogen_sync_procs = N'true', 
    @allow_queued_tran = N'true'

-- Add a snapshot job.
EXEC sp_addpublication_snapshot 
    @publication = @publication

-- Add the transactional articles.
EXEC sp_addarticle 
    @publication = @publication, 
    @article = @article, 
    @source_owner = N'dbo', 
    @source_object = @article, 
    @destination_table = @article, 
    @type = N'logbased', 
    @schema_option = 0x00000000000080F3, 
    @ins_cmd = N'CALL sp_MSins_Products', 
    @del_cmd = N'XCALL sp_MSdel_Products', 
    @upd_cmd = N'XCALL sp_MSupd_Products', 
    @auto_identity_range = N'false'
GO

B. SQL Server 2005 and later script to create a transactional publication

The following is an example of upgrading the previous script, which creates a transactional publication, to run successfully for SQL Server 2005 (9.x) and later versions. This publication supports immediate updating with queued updating as failover. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

-- Execute at the Distributor.
USE [distribution]

DECLARE @login AS sysname;
DECLARE @password AS sysname;
-- Specify the Windows account to run the Queue Reader Agent.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- Execute sp_addqreader_agent to create the Queue Reader Agent job. 
EXEC sp_addqreader_agent 
    @job_login = @login, 
    @job_password = @password;
GO

-- Execute at the Publisher.
USE [Northwind]
GO

DECLARE @publication AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @article AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran';
SET @publicationDB = N'Northwind';
SET @article = N'Products';
-- Specify the Windows account to run the Log Reader and Snapshot Agents.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- Enable the replication database.
EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'publish', 
    @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
EXEC sp_addlogreader_agent 
    @job_login = @login, 
    @job_password = @password, 
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

-- Add the transactional publication.
EXEC sp_addpublication 
    @publication = @publication, 
    @sync_method = N'native', 
    @repl_freq = N'continuous', 
    @status = N'active',
    @description = N'Transactional publication of Northwind.', 
    @allow_push = N'true', 
    @allow_pull = N'true', 
    @allow_sync_tran = N'true', 
    @autogen_sync_procs = N'true', 
    @allow_queued_tran = N'true',
    @replicate_ddl = 1,
    @enabled_for_p2p = N'false';

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

-- Add a transactional article.
EXEC sp_addarticle 
  @publication = @publication, 
  @article = @article, 
  @source_owner = N'dbo', 
  @source_object = @article, 
  @destination_table = @article, 
  @type = N'logbased', 
  @schema_option = 0x00000000000080F3, 
  @ins_cmd = N'CALL sp_MSins_Products', 
  @del_cmd = N'XCALL sp_MSdel_Products', 
  @upd_cmd = N'XCALL sp_MSupd_Products', 
  @auto_identity_range = N'false',
  @identityrangemanagementoption = N'manual',
  @fire_triggers_on_snapshot = N'false';
GO

C. SQL Server 2000 script to create a merge publication

The following is an example of a SQL Server 2000 (8.x) script that creates a merge publication for the Customers table. Default parameters have been removed for readability.

-- Enable the replication database.
USE [Northwind]
GO

DECLARE @publicationDB AS sysname
DECLARE @publication AS sysname
DECLARE @article AS sysname
SET @publicationDB = N'Northwind' 
SET @publication = N'NwdCustomersMerge' 
SET @article = N'Customers' 

EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'merge publish', 
    @value = N'true'

-- Add the merge publication.
EXEC sp_addmergepublication 
    @publication = @publication, 
    @description = N'Merge publication of Northwind.', 
    @retention = 14, 
    @sync_mode = N'native', 
    @centralized_conflicts = N'true', 
    @dynamic_filters = N'false', 
    @keep_partition_changes = N'false'
 
EXEC sp_addpublication_snapshot 
    @publication = @publication

-- Add the merge articles.
EXEC sp_addmergearticle 
    @publication = @publication, 
    @article = @article, 
    @source_owner = N'dbo', 
    @source_object = @article, 
    @type = N'table', 
    @description = null, 
    @column_tracking = N'true', 
    @schema_option = 0x000000000000CFF1
 GO

D. SQL Server 2005 and later script to create a merge publication

The following is an example of the previous script, which creates a merge publication, upgraded to run successfully for SQL Server 2005 (9.x) and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

-- Enabling the replication database
-- Enable the replication database.
USE [Northwind]
GO

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'Northwind';
SET @publication = N'NwdCustomersMerge';
SET @article = N'Customers';
-- Specify the Windows account to run the Snapshot Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'merge publish', 
    @value = N'true';

-- Add the merge publication.
EXEC sp_addmergepublication 
    @publication = @publication, 
    @description = N'Merge publication of Northwind.', 
    @retention = 14, 
    @sync_mode = N'native', 
    @dynamic_filters = N'false', 
    @keep_partition_changes = N'false',
    -- Only set to '90RTM' if all Subscribers are SQL Server 2005.
    @publication_compatibility_level = N'90RTM',
    @replicate_ddl = 1,
    @allow_subscriber_initiated_snapshot = N'true',
    @allow_web_synchronization = N'false',
    @allow_partition_realignment = N'true',
    @retention_period_unit = N'day',
    @automatic_reinitialization_policy = 0,
    @conflict_logging = N'both';
 
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login,
    @job_password = @password;

-- Add the merge article.
EXEC sp_addmergearticle 
    @publication = @publication, 
    @article = @article, 
    @source_owner = N'dbo', 
    @source_object = @article, 
    @type = N'table', 
    @description = null, 
    @column_tracking = N'true', 
    @schema_option = 0x0000000000034FD1,
    @partition_options = 0,
    @subscriber_upload_options = 0,
    @identityrangemanagementoption = N'manual',
    @delete_tracking = N'true',
    @compensate_for_errors = N'false',
    @stream_blob_columns = N'true';
GO

E. SQL Server 2000 script to create a push subscription to a transactional publication

The following is an example of a SQL Server 2000 (8.x) script that creates a push subscription to a transactional publication. Default parameters have been removed for readability.

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdProductTran' 
SET @subscriber = N'MYSUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
    @subscriber = @subscriber

-- Add a push subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @destination_db = @subscriptionDB, 
    @subscription_type = N'push'
GO

F. SQL Server 2005 and later script to create a push subscription to a transactional publication

The following is an example of the previous script, which creates a push subscription to a transactional publication, upgraded to run successfully for SQL Server 2005 (9.x) and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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 @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 
-- Specify the Windows account to run the Distribution Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- Add a push subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @destination_db = @subscriptionDB, 
    @subscription_type = N'push';

-- Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @job_login = @login, 
    @job_password = @password;
GO

G. SQL Server 2000 script to create a push subscription to a merge publication

The following is an example of a SQL Server 2000 (8.x) script that creates a push subscription to a merge publication. Default parameters have been removed for readability.

DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'push',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

H. SQL Server 2005 and later script to create a push subscription to a merge publication

The following is an example of the previous script, which creates a push subscription to a merge publication, upgraded to run successfully for SQL Server 2005 (9.x) and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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 @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdCustomersMerge'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 
-- Specify the Windows account to run the Merge Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @subscription_type = N'push';

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

I. SQL Server 2000 script to create a pull subscription to a transactional publication

The following is an example of a SQL Server 2000 (8.x) script that creates a pull subscription to a transactional publication. Default parameters have been removed for readability.

DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'push',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

J. SQL Server 2005 and later script to create a pull subscription to a transactional publication

The following is an example of the previous script, which creates a pull subscription to a transactional publication, upgraded to run successfully for SQL Server 2005 (9.x) and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

-- Execute at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran'; 
SET @publisher = $(Publisher); 
SET @publicationDB = N'Northwind'; 
-- Specify the Windows account to run the Distribution Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- At the subscription database, create a pull subscription 
-- to a transactional publication.
USE [NorthwindReplica]
EXEC sp_addpullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB,
    @subscription_type = N'pull';

-- Add an agent job to synchronize the pull subscription.
EXEC sp_addpullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @distributor = @publisher,
    @job_login = @login,
    @job_password = @password;
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdProductTran'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 

-- Add a pull subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @destination_db = @subscriptionDB, 
    @subscription_type = N'pull';
GO

K. SQL Server 2000 script to create a pull subscription to a merge publication

The following is an example of a SQL Server 2000 (8.x) script that creates a pull subscription to a merge publication. Default parameters have been removed for readability.

-- Execute at the Subscriber
DECLARE @publication AS sysname
DECLARE @publisher AS sysname
DECLARE @publicationDB AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @publisher = N'PUBSERVER' 
SET @publicationDB = N'Northwind' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica'

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [NorthwindReplica]
EXEC sp_addmergepullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @distributor = @publisher
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'MYSUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a pull subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

L. SQL Server 2005 and later script to create a pull subscription to a merge publication

The following is an example of the previous script, which creates a pull subscription to a merge publication, upgraded to run successfully for SQL Server 2005 (9.x) and later versions. Defaults for new parameters have been explicitly declared.

Note

Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

-- Execute at the Subscriber
DECLARE @publication  AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdCustomersMerge'; 
SET @publisher = $(Publisher); 
SET @publicationDB = N'Northwind'; 
-- Specify the Windows account to run the Merge Agent.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [NorthwindReplica]
EXEC sp_addmergepullsubscription 
  @publisher = @publisher, 
  @publication = @publication, 
  @publisher_db = @publicationDB;

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
  @publisher = @publisher, 
  @publisher_db = @publicationDB, 
  @publication = @publication,
  @distributor = @publisher,
  @job_login = @login,
  @job_password = @password;
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdCustomersMerge';
SET @subscriber = $(Subscriber);
SET @subscriptionDB = N'NorthwindReplica';

-- Add a pull subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @subscriber_type = N'local',
  @sync_type = N'automatic';
GO

Related content

• Create a Publication
• Create a Push Subscription
• Create a Pull Subscription
• View and Modify Replication Security Settings
• MSSQL_ENG021797
• MSSQL_ENG021798
• Replication System Stored Procedures Concepts
• Upgrade Replicated Databases