error in in nested try catch-

HI,

getting error like this while using nested try catch.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

also it doesnt roll back because of error, instead of other procedures,insert ,update are executing ending with wrong creations......works partially.

if the try catch is removed form subprocedure1 it works perfectly.

below is the example exactly what i use with more exec procedures in main procedure.

main procedure

begin

begin try

begin transaction

exec subprocedure 1

insert.....

update

COMMIT TRANSACTION

END TRY

BEGIN CATCH

insert into spErrorLog(spName, params, errorMsg)

values('dbo.project_inspectionproject_save', @.newprojectnumber, @.@.error)

if @.@.error <> 0

begin

if @.@.trancount > 0 ROLLBACK TRANSACTION

end

END CATCH

end

sub procedure 1

Begin Try

insert into yy(a,c,c)values(a,b,c)

select @.@.identity

End Try

Begin Catch

IF (XACT_STATE())=-1 ROLLBACK TRANSACTION

insert into spErrorLog(spName, params, errorMsg)

values(@.spName, '', @.errorMsg)

select -1

RAISERROR(@.errorMsg, @.errSeverity, 1)

End Catch

please help me. struggling with for long time.

venp..

Perhaps your RAISERROR in the called proc is not a severity level high enough to force the error in the calling sproc, thereby when the attempted COMMIT finds no active TRANSACTION, you are getting the Transaction Count error message.

Try checking @.TRANCOUNT before the commit just like you do on the ROLLBACK -OR make sure that your RAISERROR is a high enough severity level (is it over 10?) to case the CATCH failure.

|||

HI,

if i remove the try catch from the main procedure sub procedure works fine always. right now i'm using

if @.@.error >o

rollback transaction

--

in my main procedure . i'm using the above st for every transaction st. I dont want to use this old one. Please help me with try catch.()

it doesnt produce any error right now.(just without try catch on main)

my problem is some other person is working on this sub procedure. I 've the main procedure. we both are in situation ro rollback the whole if something goes wrong.

venp