This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, With this nightly build 5.0.2276.0, we got a weird error on Friday. We are currently using KeyTable identity method on a handful of tables where we are using Class Table Inheritance, but all the rest of the data model uses IdentityColumn. In a single SaveChanges call, LightSpeed was inserting a "parent" record into one of the IdentityColumn tables, and then inserting some child records into the KeyTable tables. The latter had a foreign key to the former. So LightSpeed correctly sent an insert statement to the database to insert the IdentityColumn "parent" record, and to select its scopeidentity(). Then, again correctly, LightSpeed sent the insert statements for the KeyTable tables. The problem was, the latter needed to include the foreign key value to point to the parent record. But, it didn't. It included a negative number for the foreign key value.... whereas it should have included the primary key that was returned by scopeidentity(). We reproduced this twice on Friday. But today, using the same install of our app, running on exactly the same database, I can't reproduce it!!! In summary, the problem was a KeyTable entity that had a foreign key to parent entity that used IdentityColumn. When inserting the child, LightSpeed was using a negative number as the key of the parent, instead of the (presumably) positive number that would have been returned by scope_identity(). Does this ring any bells with you guys? If not, I guess we just wait and see if it happens again. John |
|
|
First thought would be could this be a case where you are using an int for the identity and the identity value being returned is large enough to overflow? Also was the negative value -1 or just an arbitrary value?
|
|
|
It was -1048 or something like that. John |
|
|
OK. I think I've found what was going on. It's possible to get a SQL Server table into a state where the current (most recent) value of the identity column is NULL, and the next value, the one that will be assigned on the next insert, is 0. It appears that when Lightspeed gets the 0 back, as the Scope Identity following its first insert into the table, it ignores the zero, and uses whatever negative placeholder number LightSpeed may have had for the entity before it was saved. Does that sound plausible? I think that Truncate Table can put a table into the above-mentioned state. (You can check to see if a table, say named Foo, is in this state by running DBCC CHECKIDENT ( Foo, NORESEED). It will return NULL if the table is in this state.) For now, I'm attempting a fix by making sure we don't leave any tables in that state. |
|
|
Right that would make sense then although I must say Ive never seen this occur myself so this is definitely a new bit of insight into the inner workings of SQL Server. But yes if we got a 0 value back then it will be discarded and the original temp ID would be retained in that case. Thanks for the extra detail on what was occurring here :)
|
|