I have bi-directional replication setup between SQL7 and SQL2000 and I have defined range for each publisher. Every thing has been working just fine and over time I have added several distribution agents for new tables for 2 way replication. In order to be sure both the databases get to do insert in their assigned range, I have a CHECK constraint with "NOT FOR REPLICTION" setup on each replicated table.
Just recently, I have encountered 2 tables for which the check constraint throws an error saying the application is trying to insert row that violates the check constraint. Upon doing DBCC CHECKIDENTI with NORESEED option, I find out that the identity column value is incorrect even though this column has the property "NOT FOR REPLICATION".
I have fixed the problem by running DBCC CHECKIDENT with RESEED option, but after a few days the identity column value gets corrupted again. Any ideas?
I don't think the application is doing insert with "SET IDENTITY INSERT" because a) the identity column is set with NOT FOR REPLICATION property and b)I have check constraints to keep the application from inserting in the wrong range.
Any help will be deeply appreciated. Thanks!
-A
What version of SQL Server 2000 are you running? In earlier versions there was a bug in the SQL engine such that under certain conditions the identity seed can be corrupted. This bug is fixed in SP3 and after.
No comments:
Post a Comment