Duplicate key issue ... again ... and again ...

Question

Duplicate key issue ... again ... and again ...

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

sql-lover SSCoach Points: 18530 More actions · Answer 1

thomaswoof (6/9/2014)
I'm having the exact same problem. The table had at some point been reseeded while importing data from the old version of the software. So apparently somehow the reseed value was too low.
Our max identifier value was 168, but any kind of INSERT (directly from SSMS, whatever), gave the duplicate key error message, starting at 158. Each INSERT run would give the same error message with the key value one higher. After the error with key value 168, the INSERTS were successful.
So is a DBCC CHECKIDENT the only way of solving this?

Unfortunately and for my particular case, Yes.

I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.

SQL is delicious Ten Centuries Points: 1007 More actions · Answer 2

sql-lover (6/9/2014)
thomaswoof (6/9/2014)
I'm having the exact same problem. The table had at some point been reseeded while importing data from the old version of the software. So apparently somehow the reseed value was too low.
Our max identifier value was 168, but any kind of INSERT (directly from SSMS, whatever), gave the duplicate key error message, starting at 158. Each INSERT run would give the same error message with the key value one higher. After the error with key value 168, the INSERTS were successful.
So is a DBCC CHECKIDENT the only way of solving this?
Unfortunately and for my particular case, Yes.
I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.

If you don't post your code and tables, all anyone here can do is speculate.

sql-lover SSCoach Points: 18530 More actions · Answer 3

SQL is delicious (6/10/2014)
sql-lover (6/9/2014)
thomaswoof (6/9/2014)
I'm having the exact same problem. The table had at some point been reseeded while importing data from the old version of the software. So apparently somehow the reseed value was too low.
Our max identifier value was 168, but any kind of INSERT (directly from SSMS, whatever), gave the duplicate key error message, starting at 158. Each INSERT run would give the same error message with the key value one higher. After the error with key value 168, the INSERTS were successful.
So is a DBCC CHECKIDENT the only way of solving this?
Unfortunately and for my particular case, Yes.
I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.
If you don't post your code and tables, all anyone here can do is speculate.

I am not speculating. On my case, is a random problem on SQL 2012.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

sql-lover (6/10/2014)
SQL is delicious (6/10/2014)
sql-lover (6/9/2014)
thomaswoof (6/9/2014)
I'm having the exact same problem. The table had at some point been reseeded while importing data from the old version of the software. So apparently somehow the reseed value was too low.
Our max identifier value was 168, but any kind of INSERT (directly from SSMS, whatever), gave the duplicate key error message, starting at 158. Each INSERT run would give the same error message with the key value one higher. After the error with key value 168, the INSERTS were successful.
So is a DBCC CHECKIDENT the only way of solving this?
Unfortunately and for my particular case, Yes.
I've not found any workaround or fix on this. I keep checking for SQL2012 hotfixes every month. And like I said before, this is not a code issue and everything was perfectly fine on SQL 2008 and SQL 2005.
If you don't post your code and tables, all anyone here can do is speculate.
I am not speculating. On my case, is a random problem on SQL 2012.

Nobody said you are speculating. The problem is we don't know what your tables and your code is like. Therefore WE are speculating.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/