February 26, 2003 at 9:20 pm
I had a wierd situation on data loss.
Table A has an identity column, doc_id. And table B has its own identity column and doc_id, which is a foreign key references to A.
I inserted data into A, get max(doc_id) from A, and then insert max(doc_id) into B. I had got incorrect identity using @@identity, so I use max(doc_id).
A had 100 rows inserted, but only 1 row found with doc_id = 1001. 100 rows found in B with same doc_id = 1001 which are not right.
A lost 99 rows, B got duplicated doc_id, looks like data were not inserted to A. Because B got same max(doc_id) for 99 times. But A's identity increased, next identity doc_id was 1101.
It's a SQL Server 2000 on Windows 2000 used for billing system Nterprise. This situation happened only once for the DB running 1 year already. Does anyone know what's the problem?
Thanks in advance.
February 27, 2003 at 8:25 am
Good place to start is running dbcc checkident to make sure the idents are ok. Depending on that, you may need to reseed/repair if problems found. You should also probably run dbcc checkdb. I'd also recommend use scope_identity() rather than @@identity since the later will return the last ident value, not necessarily the one from the table you originally inserted into. A trigger on table a that inserts into table b that also has an ident will give you the ident from table b in @@identity.
Andy
February 27, 2003 at 8:32 am
Thanks, Andy.
How often do I need to run dbcc checkident?
February 27, 2003 at 12:39 pm
Rarely. I've had zero problems with idents. Doesnt mean it cant happen, but I think in practice its very rare. Suggested in this case just to be sure!
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply