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
michael_vitaleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

michael_vitaleAuthor Commented:
Edited text of question.
0
ahoorCommented:
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
0
michael_vitaleAuthor Commented:
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?
0
albertspCommented:
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
0
albertspCommented:
Michael,

the above is an answer.
When you dont use a save tran in the second proc and you transaction is rolled back, your @@trancount is reset to 0.
The first stored proc however gets a rollback command but @@trancount is already 0. This gives you the errormesseages you
mentioned above.

Albert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.