Identity Insert Problems

hortoristic · April 13th, 2004, 11:08 AM

We are migrating from Sybase to SQL2k. All objects and data have been moved. I'm seeing problems where Identity values are incorrect.

For example the next identity value for my problem table, should be: 468729 but when I run the SP - the Identity column returned is: 425976

That identity was used long ago and other tables are lined up with those keys already.

I suspect when I migrated the tables, somehow my identity columns got seeded wrong and are incrementing at the wrong points. Any suggestions how can I fix my identity column counter?

I have ran DBCC DBREINDEX and UPDATE STATISTICS on this particular table.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

jemacc · April 13th, 2004, 11:56 AM

how are you moving the tables?

if you are using the DTS make sure that in the transformation you are allowing identity insert.

hortoristic · April 13th, 2004, 12:03 PM

I have solved the problem. I was aware that @@Identity would return the last record updated in a SP - but there seems to be a difference between Sybase and M$.

If you have triggers on a table and do an insert - then immediatly capture the @@identitiy - it returns the id from the original insert - not the triggers - whereas M$ returns the id from the last trigger.

Using Scope_identity() instead of @@Identity solved the problem. The reason I was thinking it was a pointer problem was the id being returned in the trigger was nearly the same id I was expecting from the original insert - thereby suggesting to me that new identity's seemed to be returning values already used.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200