michael_vitale
asked on
Tran Count and nesting SPs
i am having a problem with a nested transaction and am receiving the following error:
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0."
what does this usually indicate?
the sps look something like the following:
create proc first_proc
begin transaction
exec @ret = second_proc
if (@ret != 0 )
begin
rollback transaction
return
end
commit transaction
return
--------------------------
create proc second_proc
begin transaction
..something fails..
if @ret != 0
begin
rollback transaction
return -1
end
commit transaction
return 0
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0."
what does this usually indicate?
the sps look something like the following:
create proc first_proc
begin transaction
exec @ret = second_proc
if (@ret != 0 )
begin
rollback transaction
return
end
commit transaction
return
--------------------------
create proc second_proc
begin transaction
..something fails..
if @ret != 0
begin
rollback transaction
return -1
end
commit transaction
return 0
Michael,
You must be working in chained mode.
For your procedure to work, you must turn it off.
Check with 'select @@tranchained' if you're working chained
or not, 1 = chained.
Using 'set chained off' will allow you to run the procedures.
You can set this in the procedure.
Arjan
You must be working in chained mode.
For your procedure to work, you must turn it off.
Check with 'select @@tranchained' if you're working chained
or not, 1 = chained.
Using 'set chained off' will allow you to run the procedures.
You can set this in the procedure.
Arjan
ASKER
I thought the same thing too initially but i am not in chained mode. Just for kicks I have tried both ways and when I am in chained mode Sybase specifically mentions chained mode in the error. Any other ideas?
Michael,
You should try using a save tran in the nested stored procedure.
It must look like
create proc proc2
as
if (@@trancount > 0)
save tran tran_name
else
begin tran tran_name
if @@error <>0
begin
rollback tran tran_name
return -1
end
else commit tran tran_name
Putting a save tran in the nested stored proc will not raise the trancount value. For some reason the trancount value is not set back to 0 after all the 'return' in your stored procs
Good Luck, Albert
You should try using a save tran in the nested stored procedure.
It must look like
create proc proc2
as
if (@@trancount > 0)
save tran tran_name
else
begin tran tran_name
if @@error <>0
begin
rollback tran tran_name
return -1
end
else commit tran tran_name
Putting a save tran in the nested stored proc will not raise the trancount value. For some reason the trancount value is not set back to 0 after all the 'return' in your stored procs
Good Luck, Albert
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER