Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mike2265
Starting Member
6 Posts |
 Posted - 2005-12-15 : 22:20:33
|
| Hello allNot sure if this one has an easy solution but anyway...We're returning @@Identity (via a connection.execute in VB5 and .Net) to return the latest identity created by the current user. We're noticing however that it doesn't always return the right value. Most of the time it will but on occasions it returns either a 0 (most frequently) or an ID from a completely different table. Note that the behaviour is not consistant within a particular process in that most of the time it will work fine to the point that it works successfully for ,000's of records a day and will only fail once a day or so(guestimate). Previously we've only managed to capture it returning 0, which, although a pain, can be captured in code, but we've just identified where it's returning an ID from another table which can cause serious problems if it is a valid ID for that table (fortunately we don't think this has happened). In this instance it returned an ID added previous to the record just added. (Most likely just before.) Where it is returning a 0 I have put in some code to cycle, say, 20 times, pausing a few milliseconds on each cycle to see if it's updated. From what I can see it doesn't. Unfortunately I can't reproduce the problem in the lab.Does anyone know how @@Identity is refreshed and if it's possible to check whether it has been updated. It would be nice to know how it actually works so I can predict its behaviour - and either cater for it or scrap using it (a real pain). Does it get set to 0 at any point if a non record add process is run?I can't use IDENT_CURRENT('tablename') as the table could well be updated by either someone else or by replication jobs which are regularly running (does replication complicate things?). And, as I understand SCOPE_IDENTITY, we are returning the identity value outside of the scope where it's created - though I haven't really gotten my head around what a scope is yet. Please someone come back and say "use the new function IDENT_CURRENT_USER('tablename') to return the last identity created by the current user in said table that happens to be much more reliable" but I suspect it won't be that simple. Unfortunately this thing is interwoven into our current application framework so moving to specific filters/finds on each table will be both a pain to impliment and slow to execute.Oh, and we're using SQL 2000 sp4. Perhaps it's an ADO problem ...Cheers and Thanks,Mike. |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-12-16 : 00:56:30
|
| One question..Does that table is having triggers which is inserting/updating records into another table?Surendra |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-16 : 03:25:19
|
quote:
Originally posted by Mike2265
And, as I understand SCOPE_IDENTITY, we are returning the identity value outside of the scope where it's created - though I haven't really gotten my head around what a scope is yet.
a scope is..a batch, transaction, which basically is a unit of work to be completed or rolled back. rolled back if an error occurs and commited if sucessfulread up on transactions, batches, and ACID in your case its best to use the SCOPE_IDENTITY() function and not @@identity or ident_curenti only browsed thru your problem, so forgive me if i forgot something.i advice u paste your code hereAfrika |
|
|
|
|
|
|
|
|
@@Identity returning the wrong record.