Export (0) Print
Expand All
Expand Minimize
0 out of 1 rated this helpful - Rate this topic

sp_addmergepullsubscription (Transact-SQL)

Updated: 15 September 2007

Adds a pull subscription to a merge publication. This stored procedure is executed at the Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions


sp_addmergepullsubscription [ @publication= ] 'publication' 
    [ , [ @publisher= ] 'publisher' ] 
    [ , [ @publisher_db = ] 'publisher_db' ] 
    [ , [ @subscriber_type= ] 'subscriber_type' ] 
    [ , [ @subscription_priority= ] subscription_priority ] 
    [ , [ @sync_type= ] 'sync_type' ] 
    [ , [ @description= ] 'description' ]
[ @publication=] 'publication'

Is the name of the publication. publication is sysname, with no default.

[ @publisher=] 'publisher'

Is the name of the Publisher. Publisher is sysname, with a default of the local server name. The Publisher must be a valid server.

[ @publisher_db =] 'publisher_db'

Is the name of the Publisher database. publisher_db is sysname, with a default of NULL.

[ @subscriber_type=] 'subscriber_type'

Is the type of Subscriber. subscriber_type is nvarchar(15), and can be global, local or anonymous. In SQL Server 2005, local subscriptions are referred to as client subscriptions and global subscriptions are referred to as server subscriptions. For more information, see the "Subscription Types" section in How Merge Replication Detects and Resolves Conflicts. A value of anonymous is required if you want to create a subscription without registering the subscription at the Publisher. This is necessary for cases such as Web synchronization, in which you cannot establish a SQL Server connection to the Publisher during subscription configuration.

[ @subscription_priority=] subscription_priority

Is the subscription priority. subscription_priority is real, with a default of NULL. For local and anonymous subscriptions, the priority is 0.0. The priority is used by the default resolver to pick a winner when conflicts are detected. For global subscribers, the subscription priority must be less than 100, which is the priority of the publisher.

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

ms189456.note(en-US,SQL.90).gifNote:
We do not recommend specifying a value of none. For more information, see Initializing a Merge Subscription Without a Snapshot.

[ @description=] 'description'

Is a brief description of this pull 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.

0 (success) or 1 (failure)

sp_addmergepullsubscription is used for merge replication.

If using SQL Server Agent to synchronize the subscription, the sp_addmergepullsubscription_agent stored procedure must be run at the Subscriber to create an agent and job to synchronize with the Publication.

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


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

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

-- Publication must support anonymous Subscribers.
-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @websyncurl AS sysname;
DECLARE @security_mode AS int;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'AdvWorksSalesOrdersMergeWebSync';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks';
SET @websyncurl = 'https://' + $(WebServer) + '/WebSync';
SET @security_mode = 0; -- Basic Authentication for IIS
SET @login = $(Login);
SET @password = $(Password);

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

-- 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,
	@use_web_sync = 1,
	@internet_security_mode = @security_mode,
	@internet_url = @websyncurl,
	@internet_login = @login,
	@internet_password = @password;
GO

Release History

15 September 2007

New content:
  • Added a note that specifying a value of none for the @sync_type parameter is not recommended.

12 December 2006

Changed content:
  • Removed content that incorrectly stated that anonymous subscriptions are deprecated.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.