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 |
|
smh
Yak Posting Veteran
94 Posts |
 Posted - 2009-03-22 : 21:41:32
|
| I have an insert stored procedure that calls another stored procedure to get get a value. Both have transaction code and try catch. If there is an error in the called procedure, the caller stored procedure is aborted and the vb.net app that called this receives an error.However, the identity number of the table that the insert procedure (the caller) refers to has the number incremented even though nothing is inserted in it.How can I prevent this from happening because it causes unnecessary gaps in the table key. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-22 : 23:07:09
|
| The value of an identity column, being a surrogate key, should be meaningless. Gaps are normal. What is the problem wih having gaps in the sequence? You know you will have them for other reasons - like when a row is deleted - this is just another time that that can happen. The only way to avoid it in this case would be to anticipate and check for the error prior to attempting the transaction.Be One with the OptimizerTG |
 |
|
|
|
|
|
problem with identity in nested transaction