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

Error handling in nested stored procedures

I have a problem with my error handling in nested stored procedures (SQL
Server 2000).
Below is an example of stored procedures that are causing this problem. If
you install the scripts and run sp1, you'll get 2 errors:
(1) Divide by zero error encountered. -- This is expected due to : select 1/
0
(2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
This 2nd error is the problem and I'm not sure what is it.
If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')",
where Col1 is an int, I get : Syntax error converting the varchar value
'hello' to a column of data type int. BUT I don't get that 2nd error about
the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
error, my error handling works perfectly.
Does anyone know what is causing this problem, and how I should change my
error handling to avoid it.
Thanks,
Craig
CREATE proc sp2
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
select 1/0
select @.err=@.@.error if (@.err<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SP
GO
CREATE proc sp1
as
declare @.err int
declare @.retvalue int
if (@.@.trancount=0) begin transaction
exec @.retvalue = sp2
select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
if (@.@.trancount>0) commit transaction
return 0 -- success
error:
if (@.@.trancount<>0) rollback transaction
if (@.err<>0) return @.err -- error
else if (@.retvalue<>0) return @.retvalue -- error returned from SPIn SQL Server, you get this error if you, for instance, begin a transaction
in one procedure and end
it in another procedure. In short, if you exist a procedure with a different
@.@.TRANCOUNT compared to
when you entered the procedure, you get this error. The reason why it seems
to work when you assign
'hello' to an int is that SQL Server will stop execution in such case. In sh
ort, have the
transaction handling in the outermost procedure. Or, you can in the inner pr
ocedure check
@.@.TRANCOUNT when you enter and SAVE TRAN if it was > 0 and in such case do t
he rollback to the
savepoint, but you still have to communicate to the caller that you didn't p
erform the work so the
caller can ROLLBACK its work. Check out the error handling articles at www.sommark
og.se for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:8F9E1557-B00E-48BB-AA9F-1C7436C1B547@.microsoft.com...
>I have a problem with my error handling in nested stored procedures (SQL
> Server 2000).
> Below is an example of stored procedures that are causing this problem. If
> you install the scripts and run sp1, you'll get 2 errors:
> (1) Divide by zero error encountered. -- This is expected due to : select
1/0
> (2) Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> This 2nd error is the problem and I'm not sure what is it.
> If I change "select 1/0" to "insert into TestTable(Col1) values ('hello')"
,
> where Col1 is an int, I get : Syntax error converting the varchar value
> 'hello' to a column of data type int. BUT I don't get that 2nd error about
> the COMMIT or ROLLBACK TRANSACTION statement missing. For this different
> error, my error handling works perfectly.
> Does anyone know what is causing this problem, and how I should change my
> error handling to avoid it.
> Thanks,
> Craig
>
> CREATE proc sp2
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> select 1/0
> select @.err=@.@.error if (@.err<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
> GO
> CREATE proc sp1
> as
> declare @.err int
> declare @.retvalue int
> if (@.@.trancount=0) begin transaction
> exec @.retvalue = sp2
> select @.err=@.@.error if (@.err<>0)or(@.retvalue<>0) goto error
> if (@.@.trancount>0) commit transaction
> return 0 -- success
> error:
> if (@.@.trancount<>0) rollback transaction
> if (@.err<>0) return @.err -- error
> else if (@.retvalue<>0) return @.retvalue -- error returned from SP
>