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 TSQL

I have the following codes in TSQL to run. I set the last insert statement to have error. But I did not get the rollback tran.

What i got is:
Here goes the transactions
(10 rows)
(12 rows)
(13 rows)
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name '#manageril1'.
======================================
begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
insert into prof_compo select * from #compoins
insert into apps_user select * from #appluserins
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
else
Commit tran
print 'commit tran'
go
=====================================
What could I have not done or missed out? Why the rollback doesnot work?

Please advice.

Rgds,
Sam.From BOL:

A batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed.

A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

Most run-time errors stop the current statement and the statements that follow it in the batch.

A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

Some recommendations about your case:

- check for errors after every insert (update,delete) statement;
- create sp and check return value (rollback if needs).|||The problem isn't in your rollback. Your IF statement is never executed because the procedure fails critically before it gets to it. @.@.ERROR registers constraint and primary key violations, but if a gross syntax error causes the process to crash then its not going to help.

blindman|||And the error handling is incorrect
@.@.error is reset by every sql statement. You might want to set a variable or goto an error handler on errror.

begin tran
print 'Here goes the transactions'

insert into profile select * from #profins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into prof_compo select * from #compoins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into apps_user select * from #appluserins
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
insert into manager select * from #manageril1
if @.@.error <> 0
BEGIN
ROLLBACK TRAN
print 'rollback'
Return
END
Commit tran
print 'commit tran'