How to: Upgrade Replication Scripts (Replication Transact-SQL Programming)

Transact-SQL script files can be used to programmatically configure a replication topology. For more information, see Programming Replication Using System Stored Procedures. The replication agent security model been enhanced and differs from SQL Server 2000. For detailed information about the security model, see Replication Agent Security Model. If you are a member of the sysadmin fixed server role in SQL Server 2005 and you run replication scripts created from SQL Server 2000 or SQL Server 7.0, the scripts will execute properly. If you are a member of the db_owner fixed database role or another role, the scripts will fail with errors MSSQL_ENG021797 or MSSQL_ENG021798 and must be upgraded.

Security Note:
Although it is not required to upgrade scripts that are executed by members of the sysadmin role, it is recommended to specify an account for each agent with the minimum privileges described in the "Permissions Required By Agents" section of the topic Replication Agent Security Model and then modify existing scripts as described in this topic.

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 earlier versions of SQL Server, 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 earlier versions of SQL Server, 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 earlier versions of SQL Server, 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 earlier versions of SQL Server, this was done automatically when a transactional publication that supported queued updating was created.

In this new security model, 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.

Security Note:
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.

Note

This example uses the Northwind sample database, which does not ship with Microsoft SQL Server 2005. To use this database with SQL Server 2005, detach and copy it from a Microsoft SQL Server 2000 instance and attach it to a SQL Server 2005 instance.

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 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 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 How to: Create a Pull Subscription (Replication Transact-SQL Programming).
   • 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 How to: Specify Synchronization Schedules (Replication Transact-SQL Programming). This is done after the execution of sp_addsubscription. For more information, see How to: Create a Push Subscription (Replication Transact-SQL Programming).

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

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 How to: Create a Pull Subscription (Replication Transact-SQL Programming).
   • 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 How to: Specify Synchronization Schedules (Replication Transact-SQL Programming). This is done after the execution of sp_addmergesubscription. For more information, see How to: Create a Push Subscription (Replication Transact-SQL Programming).

Example

The following is an example of a SQL Server 2000 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

The following is an example of upgrading the previous script, which creates a transactional publication, to run successfully for SQL Server 2005. 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

The following is an example of a SQL Server 2000 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

The following is an example of the previous script, which creates a merge publication, upgraded to run successfully for SQL Server 2005. 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

The following is an example of a SQL Server 2000 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

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

The following is an example of a SQL Server 2000 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

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

The following is an example of a SQL Server 2000 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

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

The following is an example of a SQL Server 2000 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

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

See Also

Tasks

How to: Create a Publication (Replication Transact-SQL Programming)
How to: Create a Push Subscription (Replication Transact-SQL Programming)
How to: Create a Pull Subscription (Replication Transact-SQL Programming)
How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)

Concepts

Programming Replication Using System Stored Procedures

Other Resources

MSSQL_ENG021797
MSSQL_ENG021798
Upgrading Replicated Databases

Help and Information

Getting SQL Server 2005 Assistance