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

• Microsoft SQL Server version 7.0

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 version 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'

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 

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

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'

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'

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug