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 when one field value is bad

Here's an example of what I'm playing with:

update TestTable

set IntValue = Cast(StringValue, as int)

flag =

BEGIN TRY

0

END TRY

BEGIN CATCH

1

END CATCH

I'm trying to take a string value holding an integer and populate an integer field with that value. But, every so often, the string value does not contain an integer (e.g. '13.9" or "CA"). That error would cause the entire column UPDATE to be rolled back. i'm trying to get around it with some kind of "TRY CATCH" construct but I don't believe it can work.

What would you suggest?

Barkingdog

No, you are right this won′t work. For me, the best Exception handling is to prevent exceptions, in your case check the value for being numeric, there is a function ISNUMERIC which can do that for you. But be careful with that function, because it checks for currency numeric only, so the value "$" which is part of a currency expression will also evaluate to true.

See more details on the site: http://www.aspfaq.com/show.asp?id=2390

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Error Handling SQL 2000

Hello,

Could it be possible to catch into a variable any type of sql server error?

I have an stored procedured that executes as a part of a cycle one stored procedured many times (one for each branch). I need to make the stored procedured to continue even when I have an error in one of the executions (one of the procedures of a branch).

For example, I execute this as a part of a While Statement

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.return_status <> 0
Begin Print ' /* Exito */'
..... update

End
Else
Begin Print '/* Error */'
....
EXEC master.dbo.xp_sendmail
@.recipients = @.mail_recipient,
@.subject = @.mail_subject,
@.message = @.mail_query

End

... But when I get an error like this, the stored just ends the cycle and the stored procedured.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Wish you can help me, thank you

I wish I could help you too. In SqL Server 2000 the only thing you can do is check the value of the error number in the @.@.error system function (used like a variable). So, you may be able to do something like this:

Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate

If @.@.error <> 0 or @.return_status <> 0

But it could stop the procedure, and it will return the error to the client for handling. In 2005 you have TRY...CATCH, but even that isn't flawless with some errors. For an excellent reference on 2000 error handling, check this article: http://www.sommarskog.se/error-handling-II.html

|||

and don't forget the first in the series:

http://www.sommarskog.se/error-handling-I.html

---

http://www.elsasoft.org

|||Thank you Louis

Error Handling problem

I have a strange behavior when executing a CLR SP:

The SP contains the following test code:

try {
int.Parse("");
}
catch (Exception ex) {
try {
command.CommandText = "raiserror (100003, 16, 1, 'parse error') with seterror";
pipe.ExecuteAndSend(command);
}
catch {
return;
}
}

Here the SQL I use to test it

decare @.e int
begin try
exec MySP
set @.e = @.@.error
print @.e
print 'phew!'
end try
begin catch
print error_message()
end catch
print 'done'

What I expect is that, since MySP throws an error, the catch block is executed and the error message is printed out.

What happens is that
@.@.error contains the correct error code 100003
the catch block does not executes
Here the output of SSMS:

Msg 100003, Level 16, State 1, Line 1
"invalid operation: parse error"
100003
phew!
done

Any Idea of why @.@.error is set but the catch block does not executes ?

just a final note: if do not use the try/catch block in the SP and let the exception be caught into the server engine, the catch block on the T-SQL is correctly executed (and it is my current solution, sigh !!!)Hi,

see this connect bug here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251376

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks from providing the link.
I'm skeptical they will fix it less time than a couple of years.

Error Handling in SQL Server 2000 - Need Help

Hi Everyone:

I have a stored procedure with the following structure and I would like to find out how to catch my error from each block, and raise them if transaction is rolled back in the end. As you can see, I am on SQL Server 2000, so no TRY CATCH functionality is supported, please let me know, specificaly I am looking for the code that would catch the raiserror msgs from each block, and return in the block all the way in the end, where it says if @.@.error condition. Thanks and here is the code:

CREATE PROCEDURE [dbo].[uspSaveLoanApplicationMain]

@.Bor_BorrowerGuid uniqueidentifier,

@.Bor_FirstName varchar(15),

@.Bor_LastName varchar(35),

--and so forth

AS

SET NOCOUNT OFF

BEGIN

BEGIN TRANSACTION

- Block 1 Starts

EXEC sp--

IF @.@.ROWCOUNT = 0

BEGIN

RAISERROR('Save Application Cosigner not updated', 1, 1)

END

Block 1 Ends

-- Similar to block 1 I have block 2,3 and so forth with similar structure, and in the end I -- commit transaction as you can see below.

COMMIT TRANSACTION

RETURN

IF @.@.Error > 0

ROLLBACK TRANSACTION

- Raise an error with the details of the exception

RETURN

END

Thanks.

u can make use of goto....

Create proc

@.a,@.b

AS

block 1

-statements

if @.@.error <>0

goto ErrorBlock

block 1

-statements

if @.@.error <>0

goto ErrorBlock

return 0

ErrrorBlock :

rollback transaction

--raise error a custom message

-- u can keep a table with custom error messages and use them to get the exact error message in goto.... or use the config variables in raiseerror and set correct values for them to form an error statement...

|||

You need to check @.@.ERROR after each block. Because error in block #1 doesn't stop execution of block #2. Also @.@.ERROR save last error id

For example, following code:

sp_addmessage 50001, 16, 'Error1'

sp_addmessage 50002, 16, 'Error1'

begin

RAISERROR (50001,16,1)

end

begin

RAISERROR (50002,16,1)

end

print @.@.ERROR

return

Msg 50001, Level 16, State 1, Line 2

Error1

Msg 50002, Level 16, State 1, Line 5

Error1

50002

Also, if severity level less than 11, then @.@.ERROR==0

|||

So what you are saying is that I can do this in the end and it should work:

IF @.@.Error > 0

ROLLBACK TRANSACTION

PRINT @.@.Error

RETURN

Because I am already doing RAISEERROR if you notice my code block where the sp is called, please clarify/confirm.

BEGIN

EXEC dbo.uspSaveApplicationReference @.CoborRef2

IF @.@.ROWCOUNT = 0

BEGIN

RAISERROR('Save Cosigner Reference 2 not updated', 1, 1)

END

END

Thanks and please let me know.

|||

yes....as whenever an error occurs, we r passing the control to GOTO... so u need to handle it just there...

have a look at this link ... http://www.codeproject.com/database/sqlservertransactions.asp

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthur
You're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegro ups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>
|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo a
nd you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the clie
nt. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation
at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!