sp_reinitmergesubscription (Transact-SQL)

Marks a merge subscription for reinitialization the next time the Merge Agent runs. This stored procedure is executed at the Publisher in the publication database.

Transact-SQL Syntax Conventions

Syntax

sp_reinitmergesubscription [ [ @publication = ] 'publication'
    [ , [ @subscriber = ] 'subscriber'
    [ , [ @subscriber_db = ] 'subscriber_db'
    [ , [ @upload_first = ] 'upload_first'

Arguments

• [ @publication = ] 'publication'
  Is the name of the publication. publication is sysname, with a default of all.

• [ @subscriber = ] 'subscriber'
  Is the name of the Subscriber. subscriber is sysname, with a default of all.

• [ @subscriber_db = ] 'subscriber_db'
  Is the name of the Subscriber database. subscriber_db is sysname, with a default of all.

• [ @upload_first = ] 'upload_first'
  Is whether changes at the Subscriber are uploaded before the subscription is reinitialized. upload_first is nvarchar(5), with a default of FALSE. If true, changes are uploaded before the subscription is reinitialized. If false, changes are not uploaded.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_reinitmergesubscription is used in merge replication.

sp_reinitmergesubscription can be called from the Publisher to reinitialize merge subscriptions. We recommend re-running the Snapshot Agent as well.

If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

Permissions

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

Example

-- 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 @subscriptionDB AS sysname;
DECLARE @publication AS sysname;
SET @subscriptionDB = N'AdventureWorksReplica';
SET @publication = N'AdvWorksSalesOrdersMerge';

USE [AdventureWorksReplica]

-- Execute at the Publisher to reinitialize the push subscription. 
-- Pending changes at the Subscrber are lost.
EXEC sp_reinitmergesubscription 
    @subscriber = $(SubServer),
    @subscriber_db = @subscriptionDB,
    @publication = @publication,
    @upload_first = N'false';
GO

-- Start the Merge Agent.

-- 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 @subscriptionDB AS sysname;
DECLARE @publication AS sysname;
SET @subscriptionDB = N'AdventureWorksReplica';
SET @publication = N'AdvWorksSalesOrdersMerge';

USE [AdventureWorksReplica]

-- Execute at the Publisher to reinitialize the push subscription, 
-- and upload pending changes at the Subscriber. 
EXEC sp_reinitmergesubscription 
    @subscriber = $(SubServer),
    @subscriber_db = @subscriptionDB,
    @publication = @publication,
    @upload_first = N'true';
GO

-- Start the Merge Agent.

See Also

Reference

System Stored Procedures (Transact-SQL)

Other Resources

How to: Reinitialize a Subscription (Replication Transact-SQL Programming)
Reinitializing a Subscription

Help and Information

Getting SQL Server 2005 Assistance