Merge Synchronization Error

Question

I have a merge publication set up using parameteriszed row filtering where I am overriding HOST_NAME() and the articles of this publication are set to partition_options=3 (Nonoverlapping, only goes to one subscriber).  The subscriptions are set up as PULL subscriptions.  After performning an initial snapshot to 50 remote "site" servers, I loaded hostorical data form an old system into one of the site databases and started a sync, expecting all the data to be pushed from the subscriber to the publisher.  Most of the tables uploaded fine, but 1 table, VisitInvoiceItems, has been in error the past error with the error message "The merge process is retrying a failed operation made to article 'VisitInvoiceItem' - Reason: 'One or more rows updated in table 'VisitInvoiceItem' were out of partition while the table was published with 'partition_options' set to 3." So it is obvious the site I am loading is site 523, so I selected distinct values in this table on my subscriber for that filtered column and sure enought there is only 1 distinct SiteID, 523.  I turned on verbose logging and see nothing more than this erorr and nothing appears in the error log in publisher ir subscriber, nor anything in MSrepl_errors.  I am attaching a few screen shots and am looking for a way to track down what the problem may be.

This is the subscription properties taken form the subscriber (Shows HOST_NAME() = 523)

Here are publication properties of that one article, VisitInvoiceItems (Shows non overlapping, partition_options=3)

I am at a loss on where to head from here as structurally, everything seems set up correctly.

Any help would be greatly appreciated

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 1

Basically this is saying one of two things.

There is data which has been entered into this table which does not match the filtering criteria is FilterColumn<>523 or there is some data downstream which is entered into one of the child tables of VisitiInvoiceItem which is not part of this partition.

Are there any child tables of this table?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

There are several child tables but I just verified none of them have a SiteID other then 523. 

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 3

Is there a way to view the contents of msmerge_contents in a readable way so I can see the data for that one article.  I know how to do this for transactional replication in the repl_commands but not sure how to do this with merge replication

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 4

You might want to use sp_showpendingchanges for this.

Other than that you can do something like this which will give you the rows in msmerge_Contents from this particular table - but not from any of the child tables nor any top level tables which form part of the partition

declare @nickname varbinary(16)
select @nickname =nickname From sysmergearticles where name='testtable'
print @nickname 
select * From msmerge_contents 
join msmerge_genhistory on msmerge_genhistory.generation=msmerge_contents.generation 
and msmerge_genhistory.art_nick=msmerge_contents.tablenick
where msmerge_genhistory.art_nick=@nickname 
and genstatus in (0,4)
 

which might make this table in motion.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 5

Thanks Hilary, that was able to show me the number of changes, I am still unable to get past the original error where it says data was out of partition when the data in the subscriber table all has the partition value expected

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 6

Join the above query against the base table joining on the rowguid column. then filter on distinct SiteID to ensure that it is always equal to 523. I don't know anything about your join filter hierarchy so I can;'t tell you everywhere to look.

But it is likely here - or you are running into a bug.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 7

I did that and attached are the results, all are 523.  One thing to note, I had nothing with a genstatus in(0,4) they were all equal to 1

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 8

GenStatus of 1 means the generation was closed locally. This means that the generation is not open or in error.

It looks like the error is happening with a different table. Are there any join filters on tables referencing this table? Any nulls removed from the aggregate?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 9

There are no join filters at all, only filtering is overriding the host_name on all articles.  There were no nulls removed form the aggregate (changed jointo VisitInvoiceItem to a left join) and still just got back SiteID=523, genstatus=1.  I will agree the generation completed without error, the error seems to be when trying to apply these to the publisher.  I have traced the publisher and see nothing except inserts into #retry tables, I cannot see any actual DML statements accurring.  I was capturing RPC:Completed RPC:StmtCompleted, SQL:StmtCompleted, and SQL:BatchCompleted.

Below is distinct subset_joinclause from sysmergearticles for this publication

Convert(char,Site_Id) = Host_Name()
convert(char,Site_Id)=Host_Name()
convert(char,Siteid) = host_Name()
convert(char,SiteId)= Host_Name()
convert(char,SiteId)=Host_Name()

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 10

Has there been a partition realignment? For example have you changed the hostname value for this particular subscriber? How did you deploy the subscriber? Snapshot - or did you do a no-sync?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 11

It is part of a migration from an old system to a new system.  We deplolyed using a snapshot (basically just pushed schema since no tables at the publisher had records for 523).  Then there is a conversion routine that reads from the old system and inserts into the subscriber database, then we attempt a sync to push all that data to the publisher and at that time it is considered in sync and ready for use.  I did create dynamic snapshots for each SiteID also.  WSe are not using any tbale partitioning at this point.

You just reminded me of something, I had this Site set up using another siteID at one time to run a test, but then dropped and recreated the subscriber database after my testing , I wonder if there is some metadata somewhere that is retaining that old value.  I wouldn't think so since I dropped and re-created the subscriber database, but maybe something at the publisher?

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 12

It might be. Check sysmergesubscriptions on the publisher/subscriber.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 13

OK, sorry for the delay, here is what I was able to determine.  The development team wrote a tool what copied data form a template database into the site databases and they copied the ROWGUID's instead of letting SQL generate new ROWGUID so when synchronization occurred that ROWGUID it was tryting to insert from SiteID 523 already existed in the publisher under SiteID 528.  This was a bear to track down and a good lesson learned for the development team

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz