SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 1930
GilaMonster (3/28/2014)
Can you post the table definition and the procedure?


Gail,
That's actually a good suggestion. I'll do it later during the day.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32872 Visits: 8679
I failed to catch the follow-up post about there being an identity column involved. Sorry!

Note to all - don't answer forum posts while on a cruise!! Blink

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55271 Visits: 17709
sql-lover (3/28/2014)
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing this

sql-lover (3/27/2014)
I fixed it via DBCC command


you are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here


You misread what I said. Of you are confused.

If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)

My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.

I may try later and post table's definition though.

I'm not confused, but your post is unclear

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 1930
Perry Whittle (3/28/2014)
sql-lover (3/28/2014)
Perry Whittle (3/28/2014)
The default behaviour for an identity column now is to increase the seed after a restart or failover, etc. By doing this

sql-lover (3/27/2014)
I fixed it via DBCC command


you are likely compounding the issue you are seeing. You should not have touched it. By setting the seed back it will then increment forward and hit a value that's already previously used.
There is a trace flag -T272 to make the database engine behave like SQL Server 2008. Its all detailed here


You misread what I said. Of you are confused.

If I don't reset the ID, my jobs keep failing, they keep using a lower ID. What I did was the correct and right thing. If the job is trying to insert a record with ID equals to 8, but last value in table and memory is 10, I need to reset via DBCC because it keeps failing, creating a duplicate ID error. That's an ID and column that is auto generated by SQL server, an auto increment. For some weird reason, the value goes down (or got stuck in memory? I don't know)

My thread is to know if someone else is aware of this bug. It could be VMware, it could be the SAN. NO! It is NOT a code issue.

I may try later and post table's definition though.

I'm not confused, but your post is unclear


Let's leave it like that :-)

I said the ID to be inserted was low, when it suppose to be higher, so I don't see how that's unclear. Only way to fix is via DBCC. What I did was correct.
thomaswoof
thomaswoof
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 27
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?
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 1930
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
SQL is delicious
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 174
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
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 1930
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
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63853 Visits: 17974
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search