How to: Create a Pull Subscription (Replication Transact-SQL Programming)
Pull subscriptions can be created programmatically using replication stored procedures. The stored procedures used will depend on the type of publication to which the subscription belongs.
To create a pull subscription to a snapshot or transactional publication
At the Publisher, verify that the publication supports pull subscriptions by executing sp_helppublication (Transact-SQL).
- If the value of allow_pull in the result set is 1, then the publication supports pull subscriptions.
- If the value of allow_pull is 0, execute sp_changepublication (Transact-SQL), specifying allow_pull for @property and true for @value.
At the Subscriber, execute sp_addpullsubscription (Transact-SQL). Specify @publisher and @publication. For information about updating subscriptions, see How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming).
At the Subscriber, execute sp_addpullsubscription_agent (Transact-SQL). Specify the following:
The @publisher, @publisher_db, and @publication parameters.
The Microsoft Windows credentials under which the Distribution Agent at the Subscriber runs for @job_login and @job_password.
Note
Connections made using Windows Integrated Authentication always use the Windows credentials specified by @job_login and @job_password. The Distribution Agent always makes the local connection to the Subscriber using Windows Integrated Authentication. By default, the agent will connect to the Distributor using Windows Integrated Authentication.
(Optional) A value of 0 for @distributor_security_mode and the Microsoft SQL Server login information for @distributor_login and @distributor_password, if you need to use SQL Authentication when connecting to the Distributor.
A schedule for the Distribution Agent job for this subscription. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).
At the Publisher, execute sp_addsubscription (Transact-SQL) to register the pull subscription. Specify @publication, @subscriber, and @destination_db. Specify a value of pull for @subscription_type.
To create a pull subscription to a merge publication
At the Publisher, verify that the publication supports pull subscriptions by executing sp_helpmergepublication (Transact-SQL).
- If the value of allow_pull in the result set is 1, then the publication supports pull subscriptions.
- If the value of allow_pull is 0, execute sp_changemergepublication (Transact-SQL), specifying allow_pull for @property and true for @value.
At the Subscriber, execute sp_addmergepullsubscription (Transact-SQL). Specify @publisher, @publisher_db, @publication, and the following parameters:
- @subscriber_type – specify local for a client subscription and global for a server subscription.
- @subscription_priority – Specify a priority for the subscription (0.00 to 99.99). This is only required for a server subscription.
For more information, see Advanced Merge Replication Conflict Detection and Resolution.
At the Subscriber, execute sp_addmergepullsubscription_agent (Transact-SQL). Specify the following parameters:
@publisher, @publisher_db, and @publication.
The Windows credentials under which the Merge Agent at the Subscriber runs for @job_login and @job_password.
Note
Connections made using Windows Integrated Authentication always use the Windows credentials specified by @job_login and @job_password. The Merge Agent always makes the local connection to the Subscriber using Windows Integrated Authentication. By default, the agent will connect to the Distributor and Publisher using Windows Integrated Authentication.
(Optional) A value of 0 for @distributor_security_mode and the SQL Server login information for @distributor_login and @distributor_password, if you need to use SQL Authentication when connecting to the Distributor.
(Optional) A value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password, if you need to use SQL Authentication when connecting to the Publisher.
A schedule for the Merge Agent job for this subscription. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).
At the Publisher, execute sp_addmergesubscription (Transact-SQL). Specify @publication, @subscriber, @subscriber_db, and a value of pull for @subscription_type. This registers the pull subscription.
Example
The following example creates a pull subscription to a transactional publication. The first batch is executed at the Subscriber, and the second batch is executed at the Publisher. Login and password values 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 this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks';
-- At the subscription database, create a pull subscription
-- to a transactional publication.
USE [AdventureWorksReplica]
EXEC sp_addpullsubscription
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB;
-- 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
-- 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 this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica';
-- At the Publisher, register the subscription, using the defaults.
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N'pull',
@status = N'subscribed';
GO
The following example creates a pull subscription to a merge publication. The first batch is executed at the Subscriber, and the second batch is executed at the Publisher. Login and password values 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 this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @hostname AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks';
SET @hostname = N'adventure-works\david8';
-- At the subscription database, create a pull subscription
-- to a merge publication.
USE [AdventureWorksReplica]
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),
@hostname = @hostname;
GO
-- Execute this batch at the Publisher.
DECLARE @myMergePub AS sysname;
DECLARE @mySub AS sysname;
DECLARE @mySubDB AS sysname;
SET @myMergePub = N'AdvWorksSalesOrdersMerge';
SET @mySub = N'MYSUBSERVER';
SET @mySubDB = N'AdventureWorksReplica';
-- At the Publisher, register the subscription, using the defaults.
USE [AdventureWorks]
EXEC sp_addmergesubscription @publication = @myMergePub,
@subscriber = @mySub, @subscriber_db = @mySubDB,
@subscription_type = N'pull';
GO
See Also
Tasks
How to: Create a Pull Subscription (RMO Programming)
How to: Create a Push Subscription (Replication Transact-SQL Programming)
Other Resources
How to: Create a Publication (Replication Transact-SQL Programming)
How to: Delete a Pull Subscription (Replication Transact-SQL Programming)
Subscribing to Publications
Using sqlcmd with Scripting Variables