April 28, 2005 at 8:46 am
HI,
Here is the scenario,
in all of the stored procs i have set, SET XACT_ABORT OFF
Now,
I have a stored proc LoadWKSTNS, that calls basically Either CreateWKSTNS (SP)or UPDATEWKSTNS(SP).
Inside LoadWKSTNS whenever there is an exception, (IF @@ERROR > 0) i am calling another SP ( LogExceptions) and use GOTO ProcessNext which basically transfers control back to LoadWKSTNS (calling proc)
CreateWKSTNS is like below
BEGIN TRANSACTION
Statements..
IF (@@ERROR> 0)
BEGIN
EXEC LogExceptions
GO TO Process_Next
END
COMMIT TRANSACTION
Process_Next: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
GO
Problem Description: I am expecting, If any exception, call LogExceptions sp, and rollback main transaction. But What is happenning is, It rollsback mains transaction and also any changes made by 'LogExceptions ' SP as well.
So how can only rollback main transaction but still retain the values modified by LogException SP. I have tried to use individual transactions bolcks for ''LogExceptions ' and it did not help.
Please let me know. Thanks in Advance..
TNQDigital
April 28, 2005 at 10:38 am
Unfortunately any Table access performed before a rollback is lost no matter what. Nested transactions are a little bit intensive and difficult to manage because of the inherent limit of 32 as maximum. The best Approach I have found is to not to nest at all and construct workflow that uses only one!
After you have that you can use a table variable (which are immune to transaction handling) to record the outcome of the proccess.
For example:
Declare @Errorlog (Number int, Cause varchar(200))
begin transaction
exec yourprocedure1
if @@error <> 0
begin
insert into @errorLog(Number,Cause) values (1,'Proc1 Failed')
goto Quit
end
exec yourprocedure2
if @@error <> 0
begin
insert into @errorLog(Number,Cause) values (1,'Proc2 Failed')
goto Quit
end
exec yourprocedure3
if @@error <> 0
begin
insert into @errorLog(Number,Cause) values (1,'Proc3 Failed')
goto Quit
end
...
commit transaction
return (0) --Success
Quit:
rollback transaction
insert into LogTable(Number,Cause) --Save it on an actual Table
select Number,Cause
from @errorLog
return -1 -- Failed proccess
hth
* Noel
April 28, 2005 at 4:00 pm
Hi,
Thanks for the help.. Upon exception i was inserting exception then call, goto Processnext
Processnext : ROLLBACK TRAN
Like you said " upon exception,I have to call ROLLBACK TRAN first then insert exceptions and proceed to next record/row.. that works fine..
Thanks again for your time.
THNQDigital
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply