DBCC CHECKDB when run in BEGIN TRY reports @@ERROR=0 when error occurs and CATCH not fired - by Tony Rogerson SQL

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


13
0
Sign in
to vote
ID 262129 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 3/7/2007 6:07:19 AM
Access Restriction Public

Description

CHECKDB does not interact with BEGIN TRY, by that I mean that if the database has a corruption and checkdb is run outside the begin try block then you get @@ERROR set to 8939.

Eg. this is fine...

	dbcc checkdb( checkdbpractice ) with no_infomsgs
	print @@ERROR

Once you put it in the BEGIN TRY block then @@ERROR always reports 0 and the CATCH block does not fire, in fact SQL Server just continues to run the rest of the code in the TRY BLOCK.

Sign in to post a comment.
Posted by Umachandar [MSFT] on 3/15/2010 at 10:03 AM
Closing the issue.

--
Umachandar, SQL Programmability Team
Posted by Umachandar [MSFT] on 2/3/2009 at 6:13 PM
Hi Tony,
We will not be correcting the error handling behavior for DBCC in SQL Server 2005. The fixes do not meet the bar for making it into service packs or cumulative updates.
Btw, this issue is not unique to DBCC and it happens for other errors raised by the engine. We are looking at correcting these issues for the next major version of SQL Server and we will definitely consider this scenario.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 12/11/2008 at 5:48 PM
Dear Tony,

If what you want is to a programmatic way to find out if the database is corrupted, here is one way to do it.

DBCC CHECKDB( checkdbpractice ) WITH NO_INFOMSGS, TABLERESULTS

This will return ALL the error codes (instead of only the last one) in a table to you. You can detect database corruption by checking the table is not empty. Even better, you can look for a particular error code that you care about.

Does this work for your need?

THanks,
SQL Server Engine Team
Posted by Sara [MSFT] on 3/26/2008 at 4:40 AM
Hi Tony

As I wrote in my earlier response, we are currently investigating why running DBCC CHECKDB on a corrupted database within a TRY block is not setting @@ERROR and the CATCH block is not getting invoked. We will let you know as soon as we have more information.

The second part of the response was to explain that your repro script, as written, will always return @@ERROR = 0 even if the CATCH block is invoked, in this and other scenarios. e.g. @@ERROR will be 0 in the CATCH block even for a divide-by-zero error that that invokes the CATCH block. The intention was to alert you to ensure that you’re aware of this and can avoid querying @@ERROR in this manner in other scripting.


Thank you.
Posted by Tony Rogerson SQL on 3/24/2008 at 8:17 AM
Think you are getting confused.

The whole point of my report was that the CATCH block does not run when the database is corrupt so the point about @@ERROR is void because it never executes the CATCH block!

Tony.
Posted by Sara [MSFT] on 3/21/2008 at 11:02 AM
Hello

There are two parts to this connect item: (1) running DBCC CHECKDB on a corrupted database is not invoking the CATCH block, and (2) @@ERROR = 0 in the CATCH block.

We are currently investigating (1) and will let you know when we have more information.

For (2), you are seeing this behavior because @@ERROR must be queried in the first statement in the CATCH block otherwise the value is reset. By design, @@ERROR is cleared and reset on each statement execution. In order to retrieve the error, it must be checked immediately following the statement that raised the error. This is the same behavior as earlier SQL Server releases. Within a TRY...CATCH block, @@ERROR must be the first statement in the CATCH block, otherwise it will be cleared and will return 0. In the repro script you provided (copied below), @@ERROR has been cleared since it is preceded by another statement in the CATCH block:

BEGIN TRY
    DBCC CHECKDB( checkdbpractice ) WITH NO_INFOMSGS
    PRINT @@ERROR
    PRINT 'continue'
END TRY
BEGIN CATCH
    PRINT 'got here' -- this clears @@ERROR
    PRINT @@ERROR
END CATCH

In order to retrieve the error number using @@ERROR, make sure it is the first statement in the CATCH block. ERROR_NUMBER, on the other hand, returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block (see example below).

BEGIN TRY
    DBCC CHECKDB( checkdbpractice ) WITH NO_INFOMSGS
    PRINT @@ERROR
    PRINT 'continue'
END TRY
BEGIN CATCH
    PRINT @@ERROR -- this will return the error number
    PRINT 'got here'
    PRINT @@ERROR -- this will return 0 since @@ERROR has been cleared by the earlier statements
    -- ERROR_NUMBER and the other ERROR_ functions will retain the error information and can be queried multiple times.
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Note: ERROR_NUMBER, along with the other ERROR_ functions, will only return the error information within the scope of the CATCH block and will return NULL if called from outside a TRY…CATCH block.



Thank you.


-- SQL Server Engine Team
Posted by Sara [MSFT] on 1/30/2008 at 1:33 PM
Hello

Thank you for getting in touch with us regarding this issue. We're looking into it and will respond with more information shortly.


-- SQL Server Engine Team