Wednesday, February 20, 2008

The fix for batch deletions at Publisher when use_partition_groups is set to false In Merge Replication is indeed there!!!

Yay!

The fix is in there. Please see previous post for the original bug description. We had problems when not using partition groups on our merge replication topology, whenever there were batch deletions caused by filtering those rows were deleted at the publisher during the next replication.

The faulty stored procedure was sp_MSdelsubrowsbatch


The faulty line of code, believe it or not, a single line
on the faulty sproc:

-- change tombstone type for the non-original rows (the rows that got deleted via expansion).
update dbo.MSmerge_tombstone
set type = 1
from
(select distinct tablenick, rowguid from
#notbelong where original_row <> 1) nb,
dbo.MSmerge_tombstone ts
where ts.tablenick = nb.tablenick and
ts.rowguid = nb.rowguid
option (FORCE ORDER, LOOP JOIN)

On the sproc after CU6:
update dbo.MSmerge_tombstone
set type = @METADATA_TYPE_PartialDelete
from
(select distinct tablenick, rowguid from
#notbelong where original_row <> 1) nb,
dbo.MSmerge_tombstone ts
where ts.tablenick = nb.tablenick and
ts.rowguid = nb.rowguid
option (FORCE ORDER, LOOP JOIN)


Basically the deletions caused by filtering (Partial deletes) were marked as tombstone or user deletions.

Given the following variable declarations:

declare @METADATA_TYPE_Tombstone tinyint
declare @METADATA_TYPE_PartialDelete tinyint
declare @METADATA_TYPE_SystemDelete tinyint

and assignation:

set @METADATA_TYPE_Tombstone= 1
set @METADATA_TYPE_PartialDelete= 5
set @METADATA_TYPE_SystemDelete= 6

If you ever doubted the power of a single line of code... :-p

Happy coding guys!





Labels: ,

3 Comments:

Blogger Stjohn said...

Lizet, I don't know how to thank you with this point, you save me hours and hours of headaches with this problem.

I really wonder why there were so few people who detect this bug. The merge replication in SQL Server 2005 is a great tool to handle nomad installation.

I will follow you blow until now. A lot of great articles and informations here. And maybe one day I will be able to help you as you help me, who know ... :-)

4:34 AM  
Blogger Lizet Pena de Sola said...

This post has been removed by the author.

10:41 PM  
Blogger Lizet Pena de Sola said...

If you're a Microsoft Certified Professional you have access to the Partner's Knowledge Base that is not open to the general public. There is a KB article describing this bug (already fixed on CU 6) on this site:

https://mcp.support.microsoft.com/default.aspx?scid=kb;en-us;948464

10:45 PM  

Post a Comment

<< Home