February 5, 2018 at 8:53 am
Hi,
We have a replication job that copies data from our production environment into a testing environment. As far as I can tell, the replication completed successfully, and I'm not sure the issue is being caused by the replication or not.
We have a table containing an auto-incrementing PK column that runs from "1" to "3000". However, when we try to insert a new record, we're getting a constraint violation error:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1000)."
When I try running the insert again, the error changes a bit:
"Cannot insert duplicate key in object 'MyTable''. The duplicate key value is (1001)."
Clearly, there is a disconnect in which SQL Server thinks the next available Pk value much lower than it should be. How can I fix this?
I greatly appreciate any assistance!
February 5, 2018 at 9:08 am
Looks as if either somebody is overriding the identity property by setting identity insert on, or the identity has been reseeded. Is the table in question in the publication database, or the subscription?
DBCC CHECKIDENT ('dbo.MyTable', NORESEED)
John
February 5, 2018 at 9:17 am
Thanks for the response, John.
This is occurring in the subscription (test) DB.
After running the script you gave me, I'm getting the following message:
"current identity value '1000', current column value '3000'"
By using the following script, am I able to resolve this issue for the time being?
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 3001)
February 5, 2018 at 9:25 am
Yes, that would probably stop the errors. But you ought to find out why they're happening. Is it replication that's attempting to do the inserts? If so, either the article for that table isn't set up properly, or something's gone wrong with it. If it's user activity doing the inserts, you need to decide whether that's permitted. If it is, you're probably going to get more conflicts in the future. If it isn't, perhaps you could lock it down with permissions to prevent it?
John
February 5, 2018 at 10:20 am
You're right, we'll need to take a closer look at what might have caused the seeding to become misaligned, but It looks like that script may have resolved our issue for the short term.
Thanks, John!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply