We have a merge replication topology including one publisher, multiple publications and multiple subscriptions. It has been running for at least 8 months without problems.
A few days ago I was informed that my PO codes were being "changed" without any reasons, from a standard "ZWWTP/PO-0092" style to a new "ZWWT" style: characters 5 to 8 in the PO code were changed to another string being chr(0) & chr(1) & chr(0) & chr(1) on some servers
I reached the point where it appeared that one only of my replication/suscription processes was generating such dummy data: PO codes on the publisher and this specific subscriber did not match anymore for recently updated or added records. Codes for POs created on subscriber's side will be altered when uploaded to the publisher (staying clean on the suscriber's). POs downloaded from the publisher will be propagated with an altered PO code on the subscriber.
I was then able to clean/adjust the data on both servers with some table compare utilities + some UPDATE statements, but the very same discrepancies now appear each time a replication runs between the two servers: my clean/identical data on both servers will be set back to its non convergent state after a succesfull replication is run: same records, same values!
I don't think I left much of available ressources on the net concerning data convergence and replication. I did not find anything. I am planning to throw/rebuild the existing pubication/subscription in 3 hours, but I am still looking for a rationnal answer to my problem before it turns into a psychoanalytic issue:
Does anybody have any idea about what is going on?
PS: by the way, as the PO code is not used as natural key, this replication issue has no impact on database integrity. One more argument in favor of surrogated keys that allways work in opposition to natural keys that work most of the time, but this has been discussed somewhere else ...
EDIT: well, I did it, and it DID NOT WORK! I threw both the subscription and the publication, recreated the publication, but I was then unable to generate a snapshot. The server was not able to manage what it calls "The identity range allocation entry for the Publisher", which "could not be found in the system table MSmerge_identity_range.
After browsing, I found this article saying such a problem can occur when “You dropped the first publication that was created in the publication database”
How funny it is! This is exactly what I just did!
Fortunately, this problem is supposed to be solved with SQLServer 2005 cumulative pack 5, which I still have to download and install. But the question is now: how did the SQLServer 2005 users could work before the release of this CP5?
EDIT2: the cumulative pack 5 did not work and I am still not able to create a snapshot for my new replication!