Microsoft KB Archive/246321

= 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

-

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

© Microsoft Corporation. All rights reserved.