Microsoft KB Archive/234814

= 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'', @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'', @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'\<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:
 * 1) Run the snapshot agent for publications db1 and db2 on the publisher.
 * 2) From the Pull Subscriptions folder in the db3 database, right-click each of the subscriptions and choose Synchronize now.
 * 3) The second subscription fails. Reinitialize the failed subscription and select Synchronize now again.

Keywords: kbbug kbfix KB234814

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.