Microsoft KB Archive/246321

Knowledge Base

BUG: Merge Subscriptions with NOSYNC Option May Experience Inconsistent Data on Publisher and Subscriber

Article ID: 246321

Article Last Modified on 10/16/2002

---

APPLIES TO

• Microsoft SQL Server 7.0 Standard Edition

---

This article was previously published under Q246321

BUG #: 55680 (SQLBUG_70)

SYMPTOMS

When a table has a static filter and is joined to a parent table through a join filter, if the data on the child table is updated such that the data falls out of the partition for a given subscriber the data in the parent table that is irrelevant to that subscriber should automatically be deleted.

If merge subscriptions are created with the @sync_type option set to NOSYNC in either sp_addmergesubscription or sp_addmergepullsubscription, the data in the parent table irrelevant to the subscriber is not automatically deleted.

CAUSE

The initial merge process fails to BCP in the sysmergesubsetfilters table to the subscriber when the subscription is created with the @sync_type option set to "NOSYNC".

WORKAROUND

To work around this behavior use either of the following:

• Do not use the manual synchronization option for creating merge subscriptions. Use @sync_type ='automatic' only.
  

-or-

• Manually insert the contents of the sysmergesubsetfilters table from the publisher to the subscriber.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

For example, table A is the parent table and table B is the child table. The data in the tables would be:

TABLE_A
1, 1000, 'Pub (Parent) [A] #1'
2, 1000, 'Pub (Parent) [A] #2'
3, 1000, 'Pub (Parent) [A] #3'

TABLE_B
1, 1000, 1, 'Pub->Sub (Child) [B] #1'
2, 1000, 1, 'Pub->Sub (Child) [B] #2'
3, 1000, 1, 'Pub->Sub (Child) [B] #3'

The articles are created as follows:

exec sp_addmergearticle @publication = N'PUB', @article = N'TABLE_A', @source_owner = N'dbo', @source_object = N'TABLE_A', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'none', @creation_script = null, @schema_option = 0x0000000000000081, @article_resolver = null, @subset_filterclause = null
GO
exec sp_addmergearticle @publication = N'PUB', @article = N'TABLE_B', @source_owner = N'dbo', @source_object = N'TABLE_B', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'none', @creation_script = null, @schema_option = 0x0000000000000081, @article_resolver = null, @subset_filterclause = N'STATUS = 1'
GO
exec sp_addmergefilter @publication = N'PUB', @article = N'TABLE_A', @filtername = N'MF_B_A', @join_articlename = N'TABLE_B', @join_filterclause = N'[TABLE_A].ID = [TABLE_B].ID', @join_unique_key = 0
GO
                

Table_B is updated as follows on the publisher:

update TABLE_B set STATUS = 0 where ID = 3 and GID = 1000
                

The expected data on the subscriber should be:

TABLE_A
1, 1000, 'Pub (Parent) [A] #1'
2, 1000, 'Pub (Parent) [A] #2'

TABLE_B
1, 1000, 1, 'Pub->Sub (Child) [B] #1'
2, 1000, 1, 'Pub->Sub (Child) [B] #2'

However, the actual data on the subscriber would be:

TABLE_A
1, 1000, 'Pub (Parent) [A] #1'
2, 1000, 'Pub (Parent) [A] #2'
3, 1000, 'Pub (Parent) [A] #3'

TABLE_B
1, 1000,1, 'Pub->Sub (Child) [B] #1'
2, 1000,1, 'Pub->Sub (Child) [B] #2'

Keywords: kbbug kbpending KB246321

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.