[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Try/Catch block is altering error message

Smoker

3/20/2007 8:57:00 PM


I have noticed that the addition of a try/catch block alters a specific
error caused by a stored procedure I have. The following code is a
simplified version. I understand the error (return with no commit/rollback)
and have taken steps to fix, but I was wondering if anyone thinks if this is
a bug or not. Adding a try/catch block to the procedure spATest will result
in a different error message even though the error in question does not flow
through the catch handler.


create proc [dbo].[spATest]
as
begin
begin transaction
return -- this generates an error
commit transaction
end
go

create proc [dbo].[spATest2]
as
begin
BEGIN TRY
begin transaction
return -- this generates an error
END TRY
BEGIN CATCH
print 'test' -- will not be executed
rollback transaction
END CATCH
commit transaction
end
go

exec spATest
go
exec spATest2
go

yields


Msg 266, Level 16, State 2, Procedure spATest, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count = 1.

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
3 Answers

EMartinez

3/21/2007 3:17:00 AM

0

On Mar 20, 3:56 pm, "John" <nos...@com.com> wrote:
> I have noticed that the addition of a try/catch block alters a specific
> error caused by a stored procedure I have. The following code is a
> simplified version. I understand the error (return with no commit/rollback)
> and have taken steps to fix, but I was wondering if anyone thinks if this is
> a bug or not. Adding a try/catch block to the procedure spATest will result
> in a different error message even though the error in question does not flow
> through the catch handler.
>
> create proc [dbo].[spATest]
> as
> begin
> begin transaction
> return -- this generates an error
> commit transaction
> end
> go
>
> create proc [dbo].[spATest2]
> as
> begin
> BEGIN TRY
> begin transaction
> return -- this generates an error
> END TRY
> BEGIN CATCH
> print 'test' -- will not be executed
> rollback transaction
> END CATCH
> commit transaction
> end
> go
>
> exec spATest
> go
> exec spATest2
> go
>
> yields
>
> Msg 266, Level 16, State 2, Procedure spATest, Line 0
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 0, current count = 1.
>
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any, should
> be discarded.

It seems like the return keyword is in itself, a non-rollback
transaction: which leads me to believe that the try...catch block is
realistically failing to capture the error. Just my thoughts.

Regards,

Enrique Martinez
Sr. SQL Server Developer

Razvan Socol

3/21/2007 8:42:00 AM

0

Hello, John

You cannot catch such an error inside the spATest2 procedure itself.
You would be able to catch it from the calling procedure, for example:

create proc [dbo].[spATest]
as
begin
begin transaction
return
commit transaction
end

go
create proc [dbo].[spATest3]
as
begin
BEGIN TRY
EXEC spATest -- this generates an error
END TRY
BEGIN CATCH
print 'test' -- now it will be executed
rollback transaction
END CATCH
commit transaction
end

GO
EXEC spATest3

Razvan

Smoker

3/21/2007 2:51:00 PM

0

Hello Razvan

Yes, I understand the error actually occurs on the return from the
procedure, so it is outside the scope of the try/catch. My issue is that
the error message generated is altered by the presence of the try/catch,
even though the error does not flow through the try/catch.


"Razvan Socol" <rsocol@gmail.com> wrote in message
news:1174466538.961395.164780@e65g2000hsc.googlegroups.com...
> Hello, John
>
> You cannot catch such an error inside the spATest2 procedure itself.
> You would be able to catch it from the calling procedure, for example:
>
> create proc [dbo].[spATest]
> as
> begin
> begin transaction
> return
> commit transaction
> end
>
> go
> create proc [dbo].[spATest3]
> as
> begin
> BEGIN TRY
> EXEC spATest -- this generates an error
> END TRY
> BEGIN CATCH
> print 'test' -- now it will be executed
> rollback transaction
> END CATCH
> commit transaction
> end
>
> GO
> EXEC spATest3
>
> Razvan
>