I have a .net transaction with a SQL insert to a SQL Server 2005 database. The table has an identity primary key.
When an error occurs within the transaction,
Rollback() is called. The row inserts are rolled back correctly, however the next time I insert data to the table, the identity is incremented as if the rollback never occurred. So essentially there are gaps in the identity sequence. Is there any way to have the
Rollback() method reclaim the missing identity?
Am I not approaching this the right way?