Wednesday, September 26, 2012

[Replication] IDENTITY, failed inserts due to primary key, and no-longer-replicated tables

We recently had a problem where we had to break replication to several table, but keep data flowing to them from another data source.  So we built a process that inserted new records into the no-longer-replicated table

However, our inserts failed, telling us there was a problem with the Primary Key. "Violation of the primary key" because it didn't accept NULL.  Which was odd, because when you look at it the tables clearly have an identity column.  Eventually we (okay, it was JS) thought to check the identity column:

DBCC CHECKIDENT (blah, NORESEED)

Which returned NULL(!).  Simply doing

DBCC CHECKIDENT (blah, NORESEED)

Pushed the values back to a real number, at which point the inserts were able to occur.

Note that you cannot RESEED an identity back to NULL.  I have no doubt there's a way, because SQL does it for replication, but if you want to go back to replicating that table, you'll probably need to snapshot it.

No comments: