identity column rollback?

I am using a stored procedure to insert data to a table.

If there is any error then i rollback the transaction. this works fine.

but the identity column gets incremented, i dont want any of the values to be skipped due to an error as that number has to be accounted for.

do you know anyway in which this is possible to rollback the identity value from the DB?nihar,

I don't know your stored procedure processing fully, but I think using DBCC CHECKIDENT() will help you out.

Depending on what the current identity value is for the table in question in relation to any record gaps, you may want to examine IDENT_CURRENT() too.

Check BOL for details...

Hope this helps!

Kael|||thanks Kael,

that worked fine. dbcc checkident

heres what i was doing

.
.
begin transaction
insert into sometab values (somevalues)
set @.outparam = @.@.identity

insert into someother tab values (@.outparam...)
if @.@.error <> 0
begin
rollback transaction
dbcc checkident('sometab') --this is what i have added now
end
else
begin
commit transaction
end

this i have done as due to rollback the identity should not have increased.

thanks again.|||Hmmm...that's funny. I'm trying to image by looking at your code how the identity would increase, even though the transaction is being rolled back, but I can visualize it. Oh well. Looks like that DBCC command worked for you, so I'd quit while I'm ahead!

Kael|||hey it increase the value as soon as i insert into the table.

try this:
begin trans
insert into a table with identity column
select @.@.identity
rollback
insert again
check identity column it will have increased skipping the one which rolled back.

if u want i can give u the entire stored proc attached: its 200 odd lines :D|||Identity is designed for multitasking.
If you insert values 1-3, others can insert 4-5. If you rollback and they don't, they must have values 4-5. So you must write your own multitasking code for values without gaps.

Good luck!|||If that is the case then what is the purpose of identity.

Can you tell me what will happen if i insert values 1-3 then rollback, others insert 4-5 and save.
after rollback i call the 'dbcc checkident' proc. what happens then? Is it correct to do that?

if not proper what alternatives do I have to consider?|||You can use dbcc checkident reseed, but only if you are ADMISTRATOR.
No user can use it even by trigger. I recomend something like SP with

begin tran
INSERT TABLEX(XID)
select max(XID)+1
from TABLEX (XLOCK)
.
.
.
if ...
ROLLBACK
else
COMMIT

I am not sure about the level of locking used. I cannot use BOL now.

Good luck!|||Tell me what happens when u lock the insert max, and someone else call the max of whatever.. if u get 3, he will also get 3 since u havent committed yet.

in this case what happens, you have to trap a primary key violation and call insert again?

u will have to lock the table in that case..

or what else?|||He must wait, the same for dbcc checkident.|||it Wouldnt be ideal as conflicts will also arise when someone is editing the table.

where time would be the essence this wont really work. i have seen it happen.. even though the lock is for the minimal of time, any procedure which has to wait for another to release isnt the ideal construct.|||A. 1, 2, 3 -> one user or else high locking
B. 1, 7, 45 -> an identity and no special problems

You must decide. Sometimes you must choose A :)