Problem synchronizing data using merge replication

Chris Darts

One of my subscribers has stopped replicating with the publisher in SQL Server 2005 so I am trying to append any new rows created at the subscriber to the publisher using SQL Data Compare.

The comparison seems to go fine, but when it comes to synchronizing I get the following error:

[548] The insert failed. It conflicted with an identity range check constraint in database 'FCC', replicated table 'dbo.tblMergePrint', column 'MergePrintID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

The identity column in question MergePrintID is automatically managed by replication, but I thought the fact that the SQL uses 'SET_IDENTITY_INSERT [dbo].[tblMergePrint] ON' would get around this problem.

I haven't tried running sp_adjustpublisheridentityrange as it suggests, as the sp looks quite involved and I'm loathed to mess something up which may cause problems for the other 7 subscribers.

I can't help thinking I'm missing something obvious, but if anyone could advise it would greatly appreciated. As with most of these posts I'm sure, this is fairly urgent as it's being used in a live production environment.

Many thanks,

Chris

richardjm

What it looks like is that there's an IDENTITY conflict between the tables. Did a quick google and found this article which seems to describe your situation?

http://searchsqlserver.techtarget.com/t ... 31,00.html

I suppose the only thing I can suggest trying (and this could be risky) is to perform a data compare without the IDENTITY column as that should get auto-generated on the target. However your foreign keys may be thrown off by such a strategy. I'd certainly test this on a test environment first.

Whatever you do with the inserts I'd certainly check your identity seed values after migration.

Not sure how much use this is to you - but I've not had my coffee yet.

Chris Darts

I'm pretty sure it won't work unless I can insert in the primary key field as the foreign keys will almost certainly be incorrect. Is there no way to drop the replication constraint on the PK before inserting the rows?

Can I just check that SQL Data Compare is designed to work with SQL Server 2005 Replication?

I saw this post the other day and it got me wondering (although it's posted for a previous version of the software):

http://www.red-gate.com/messageboard/vi ... t=2532#top

Thanks again,

Chris

richardjm

We've not specifically tested anything to do with replication. It's more used to check that replication is working.

Chris Darts

Thanks for the quick reply Richard.

I'm glad this has been brought to my attention now I'm still using the evaluation version before purchasing the full copy, as on your products page it certainly makes it seem like it works fine with replication, not just comparing but for synchronizing databases:

"One other functionality of SQL Data Compare is the ability to synch the databases so, if a replication has failed, work can continue while issues are addressed."

http://www.red-gate.com/products/SQL_Da ... simple.htm

Thanks,

Chris