A severe error occurred on the current command

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
 
A scary message, isn’t it?
Create the following stored procedure and run it in SSMS:
 

USE AdventureWorks

GO

CREATE PROCEDURE dbo.usp_Test2

AS

SET

NOCOUNT ON

BEGIN TRY

BEGIN

TRAN

SELECT

GETDATE()

RETURN

0

COMMIT TRAN

END

TRY

BEGIN

CATCH

PRINT

‘The SP failed:’+ERROR_MESSAGE();

END CATCH 

GO

EXEC

dbo.usp_Test2You get the error message. Look at the stored procedure, it’s caused by the RETURN statement, which is in front of COMMIT statement, leaving the transaction open.
The above message is even not catchable. Try the following code:

BEGIN

TRY

EXEC

dbo.usp_Test2

END

TRY

BEGIN

CATCH

PRINT

‘The SP failed:’+ERROR_MESSAGE();

END

CATCH

 

It gives the same scary message. SQL Server should have given a better message for this.
The interesting this is if you remove TRY/CATCH block from the stored procedure:
 

ALTER

PROCEDURE dbo.usp_Test2

AS

SET

NOCOUNT ON

BEGIN

TRAN

SELECT

GETDATE()

RETURN

0

COMMIT

TRAN

GO

EXEC

dbo.usp_Test2It actually gives you a more user friendly message:

Msg 266, Level 16, State 2, Procedure usp_Test2, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
 
I wonder why the TRY/CATCH statements suppressed this user friendly message and give the scary error, which is hard for developers to debug and troubleshooting.

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s