Knowledge Base

FIX: Merge Agent Fails During Synchronization of Rollup Subscriber

Article ID: 234814

Article Last Modified on 3/14/2006

APPLIES TO

Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q234814

BUG #: 55330 (SQLBUG_70)

SYMPTOMS

This problem occurs when you use Merge Replication to set up a Rollup Subscriber configuration. Multiple publishers push the same table to the same rollup subscriber table. When the second publisher attempts the initial synchronization, the Merge Agent fails with the following error message:

The process could not drop one or more tables because the tables are being used by other publications.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

This error can be avoided by using a Pull subscription instead of a Push subscription. The following options are required for a successful rollup subscriber:

The pre_creation_cmd should use 'delete'.

Replication Script: sp_addmergearticle: The pre_creation_cmd parameter must be 'delete'.

User Interface: Open the Publication properties and choose the Article tab. Next, click the ellipse button to the right of the table. Click Snapshot and choose "Delete data in existing table that matches the row filter statement".
Subscribe to the publication using a pull subscription.

Replication Script: sp_addmergepublication: @allow_pull = N'true'
sp_addmergesubscription: @subscription_type = N'pull' and @sync_type = N'automatic'

User Interface: Right-click on the target database. Choose New and then Pull Subscription. Step through the Subscription wizard. Make sure to select "Yes, initialize the schema and data at the subscriber".
Reinitialize the failed synchronizations.

User Interface: Run the snapshot agent for the publication. Then expand the target database and the Pull Subscriptions folder. For all subscriptions, right-click and choose "synchronize now" to run the initial synchronization. The merge agent for the first subscription will run correctly. The merge agent for the remaining subscriptions fails with the preceding error. Right-click the failed agent(s) and choose Reinitialize. Again right-click the failed agent(s) and choose Synchronize now.

An example of a rollup subscriber can be created using the following script. You will need to fill in the Publisher, Subscriber, and Distributor server names. This script assumes that the distribution database is already installed. You also need to modify the sp_grant_publication_access commands to include the domain user used by SQL Agent. Additional steps needed to create this model follow the script.

-- Create three databases.

    USE master
    GO
    CREATE DATABASE db1
    GO
    CREATE DATABASE db2
    GO
    CREATE DATABASE db3
    GO

-- Create the sales table in all three databases. 

    CREATE TABLE [db1].[dbo].[sales] (
       [id] [int] NOT NULL,
       [name] [char] (30) NULL ,
       [city] [char] (30) NULL,
       [guidid] uniqueidentifier rowguidcol default newid() 
       ) ON [PRIMARY]
    GO
    CREATE TABLE [db2].[dbo].[sales] (
       [id] [int] NOT NULL ,
       [name] [char] (30) NULL ,
       [city] [char] (30) NULL,
       [guidid] uniqueidentifier rowguidcol default newid() 
       ) ON [PRIMARY]
    GO
    CREATE TABLE [db3].[dbo].[sales] (
       [id] [int] NOT NULL ,
       [name] [char] (30) NULL ,
       [city] [char] (30) NULL,
       [guidid] uniqueidentifier rowguidcol default newid() 
       ) ON [PRIMARY]
    GO

-- Add the Subscriber.
    exec sp_addsubscriber @subscriber = N'<SUBSCRIBER>', @type = 0,
         @security_mode = 1, @frequency_type = 64, @frequency_interval = 1,
         @frequency_relative_interval = 2,
         @frequency_recurrence_factor = 0, @frequency_subday = 8,
         @frequency_subday_interval = 1,
         @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, 
         @active_end_time_of_day = 235900, @description = N''
    GO
    exec sp_changesubscriber_schedule @subscriber = N'<SUBSCRIBER>',
         @agent_type = 1, @active_end_date = 0
    GO

-- Enable the replication database  - db1.
   USE master
   GO
   EXEC sp_replicationdboption N'db1', N'merge publish', N'true'
   GO

-- Add the merge publication for db1.
   USE [db1]
   GO
   EXEC sp_addmergepublication @publication = N'db1', 
        @description = N'Merge publication of db1 database from
        Publisher.', @retention = 60, @sync_mode = N'native',
        @allow_push = N'true', @allow_pull = N'true',
        @allow_anonymous = N'false', @enabled_for_internet = N'false',
        @centralized_conflicts = N'true', @dynamic_filters = N'false'
   GO
   EXEC sp_addpublication_snapshot @publication = N'db1',
        @frequency_type = 8, @frequency_interval = 64,
        @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, 
        @frequency_subday = 1, @frequency_subday_interval = 0,
        @active_start_date = 0, @active_end_date = 0,
        @active_start_time_of_day = 231700, @active_end_time_of_day = 0
   GO
   EXEC sp_grant_publication_access @publication = N'db1',
        @login = N'BUILTIN\Administrators'
   GO
   EXEC sp_grant_publication_access @publication = N'db1',
        @login = N'distributor_admin'
   GO
   EXEC sp_grant_publication_access @publication = N'db1',
        @login = N'<DOMAIN>\<USER ACCOUNT>'
   GO
   EXC sp_grant_publication_access @publication = N'db1',
        @login = N'sa'
   GO

-- Adding the merge articles
   EXEC sp_addmergearticle @publication = N'db1', @article = N'sales',
        @source_owner = N'dbo', @source_object = N'sales',
        @type = N'table', @description = null, @column_tracking = N'true',
        @status = 'active', @pre_creation_cmd = N'delete',
        @creation_script = null, @schema_option = 0x00000000000000F1,
        @article_resolver = null,
        @subset_filterclause = N'sales.city  = ''Billings'' '
   GO

-- Enabling the replication database - db2
   USE master
   GO
   EXEC sp_replicationdboption N'db2', N'merge publish', N'true'
   GO

-- Adding the merge publication for db2
   USE [db2]
   GO
   EXEC sp_addmergepublication @publication = N'db2', 
        @description = N'Merge publication of db2 database from
        Publisher.', @retention = 60, @sync_mode = N'native',
        @allow_push = N'true', @allow_pull = N'true',
        @allow_anonymous = N'false', @enabled_for_internet = N'false',
        @centralized_conflicts = N'true', @dynamic_filters = N'false'
   GO
   EXEC sp_addpublication_snapshot @publication = N'db2',
        @frequency_type = 8, @frequency_interval = 64,
        @frequency_relative_interval = 0, @frequency_recurrence_factor = 1,
        @frequency_subday = 1, @frequency_subday_interval = 0,
        @active_start_date = 0, @active_end_date = 0,
        @active_start_time_of_day = 5900, @active_end_time_of_day = 0
   GO
   EXEC sp_grant_publication_access @publication = N'db2',
        @login = N'BUILTIN\Administrators'
   GO
   EXEC sp_grant_publication_access @publication = N'db2',
         @login = N'distributor_admin'
   GO
   EXEC sp_grant_publication_access @publication = N'db2',
         @login = N'<DOMAIN>\<USER ACCOUNT>'
   GO
   EXEC sp_grant_publication_access @publication = N'db2', @login = N'sa'
   GO

-- Adding the merge articles
   EXEC sp_addmergearticle @publication = N'db2', @article = N'sales',
        @source_owner = N'dbo', @source_object = N'sales',
        @type = N'table', @description = null, @column_tracking = N'true',
        @status = 'active', @pre_creation_cmd = N'delete', 
        @creation_script = null, @schema_option = 0x00000000000000F1,
        @article_resolver = null,
        @subset_filterclause = N'sales.city = ''Miles City'' '
   GO

-- Adding the merge subscriptions.

   USE db3
   GO
   exec sp_addmergepullsubscription @publication = N'db1',
        @publisher = N'<PUBLISHER>', @publisher_db = N'db1',
        @subscriber_type = N'local', @subscription_priority = 0.000000,
        @sync_type = N'automatic',
        @description = N'Merge publication of db1 database from Publisher.'
   GO
   exec sp_addmergepullsubscription_agent @publisher = N'<PUBLISHER>',
        @publisher_db = N'db1', @publication = N'db1',
        @distributor = N'<DISTRIBUTOR>', @subscriber_security_mode = 1,
        @publisher_security_mode = 1, @distributor_security_mode = 1
   GO
   use db1
   GO
   exec sp_addmergesubscription @publication = N'db1',
        @subscriber = N'<SUBSCRIBER>', @subscriber_db = N'db3',
        @subscription_type = N'pull', @subscriber_type = N'local', 
        @subscription_priority = 75.000000, @sync_type = N'automatic'
   GO
   use db3
   GO
   exec sp_addmergepullsubscription @publication = N'db2',
        @publisher = N'<PUBLISHER>', @publisher_db = N'db2',
        @subscriber_type = N'local', @subscription_priority = 0.000000,
        @sync_type = N'automatic',
        @description = N'Merge publication of db2 database from Publisher.'
   GO
   exec sp_addmergepullsubscription_agent @publisher = N'<PUBLISHER>',
        @publisher_db = N'db2', @publication = N'db2',
        @distributor = N'<DISTRIBUTOR>', @subscriber_security_mode = 1,
        @publisher_security_mode = 1, @distributor_security_mode = 1
   GO
   use db2
   GO
   exec sp_addmergesubscription @publication = N'db2',
        @subscriber = N'<SUBSCRIBER>', @subscriber_db = N'db3',
        @subscription_type = N'pull', @subscriber_type = N'local', 
        @subscription_priority = 75.000000, @sync_type = N'automatic'
   GO

Additional Steps for Example:

Run the snapshot agent for publications db1 and db2 on the publisher.
From the Pull Subscriptions folder in the db3 database, right-click each of the subscriptions and choose Synchronize now.
The second subscription fails. Reinitialize the failed subscription and select Synchronize now again.

Keywords: kbbug kbfix KB234814

Microsoft KB Archive/234814

Contents