Identity column SQL Server 2005 inserting same value twice?

Look at the error message more closely: Violation of PRIMARY KEY constraint.. where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value It looks to me like the problem is nothing to do with the identity column, as it isn't the primary key, but more likely the date/time approach is not generating a unique value and failing when you try to insert it. Is there some reason you're not just using an identity column as the primary key?

It is possible that the initial primary key generation is the problem yes (time used to the millisecond, so would have to occur at the exact same millisecond). I considered it more likely that the second generation of the primary key value was the problem (which would then mean that the identity column value, which is used for this generation,was the problem) DB schema is from an existing application, possibly this part of the application you would consider legacy! - do not know reasons for original decisions – DannykPowell May 17 '10 at 10:34 The time returned by the system clock is typically only accurate to within approx.15 milliseconds on most computers, so the insertions would only have to happen in the same 15ms period not the same millisecond; if it's possible that two insertions could happen within this margin (that's only about 65 inserts per second) then I'd expect to see the error from the initial key generation.

– Greg Beech May 17 '10 at 17:33 that's really interesting, thanks Greg...in which case the route I've suggested to the dev assigned to work on this may not be sufficient. Perhaps generating the initial key would be better to use a guid instead of time... – DannykPowell May 18 '10 at 20:49.

I think, you should use IDENT_CURRENT(‘tablename’) instead. SCOPE_IDENTITY(), will return the last identity value created in the current session. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

blog.sqlauthority.com/2007/03/25/sql-ser... Here you can read about your issue connect.microsoft.com/SQL/feedback/ViewF... and another useful article on parallelism sqlblog.com/blogs/aaron_bertrand/archive... Good Luck!

Thanks for the links. Seems to me that using IDENT_CURRENT would ensure the problem happens more often, not fixes it- it would get the last insert to my table regardless of the scope of the current running stored procedure- but that's what I'm trying to avoid. Reading the links, I suppose it is possible that the problem is not the identity column insert, but the read back out using SCOPE_IDENTITY().

I need to understand the environment fully where the problem is occuring to see if parallelism is the cause. Thanks for your help – DannykPowell May 17 '10 at 10:40 this looking likely at present- going to try using one of the suggested workarounds on one of those links of using OUTPUT clause in the insert instead of SCOPE_IDENTITY(). Will let you know how it goes/ thanks again – DannykPowell May 17 '10 at 11:08 Ok, I'll update my answer too if I'll find out anything else.

Good Luck! – hgulyan May 17 '10 at 11:16 We turn parallelism off for our SQL servers: sqlcat. Com/top10lists/archive/2008/10/01/… and: feodorgeorgiev.Com/blog/2010/03/… – Junto May 17 '10 at 17:01.

If you insert the current date/time value as an initial value for the PK, and it executes simultaneously by 2 different threads/processes at the same time chances are you get the same value.

Yes that is a possibility. The date/time is used to the millisecond so the 2 stored procedure executions would have to happen at the exact same millisecond – DannykPowell May 17 '10 at 10:30.

I think, you should use IDENT_CURRENT(‘tablename’) instead.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.