Errors from DBCC CHECKDB not caught by CATCH

I'm having problems catching errors from DBCC CHECKDB even though the errors
have a severity level above 10. I'm using the "broken" database created by
Paul Randal,
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/17/example-corrupt-database-to-play-with.aspx (see the zip file at the end).
Even though a "DBCC CHECKDB (broken)" results in two errors with severity
level 16 the following code will never end up in the CATCH block..
BEGIN TRY
DBCC CHECKDB(broken)
END TRY
BEGIN CATCH
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
Does anyone know if this is by design or a bug? I'm using SQL Server 2005
with Service Pack 2.Hi Allan,
I've reported it on connect as a bug.
Use @.@.ERROR instead and don't use BEGIN TRY.
The CATCH is never reached, in fact - if you using BEGIN TRY and DBCC
CHECKDB fails then it stays in the BEGIN block and if you check @.@.ERROR its
actually 0!
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Allan" <allan@.newsgroups.nospam> wrote in message
news:B2E2D4EC-7F2D-434C-9F0C-AEA328C57188@.microsoft.com...
> I'm having problems catching errors from DBCC CHECKDB even though the
> errors
> have a severity level above 10. I'm using the "broken" database created by
> Paul Randal,
> http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/17/example-corrupt-database-to-play-with.aspx
> (see the zip file at the end).
> Even though a "DBCC CHECKDB (broken)" results in two errors with severity
> level 16 the following code will never end up in the CATCH block..
> BEGIN TRY
> DBCC CHECKDB(broken)
> END TRY
> BEGIN CATCH
> 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
> Does anyone know if this is by design or a bug? I'm using SQL Server 2005
> with Service Pack 2.

Errors from DBCC CHECKDB not caught by CATCH

I'm having problems catching errors from DBCC CHECKDB even though the errors
have a severity level above 10. I'm using the "broken" database created by
Paul Randal,
http://blogs.msdn.com/sqlserverstor...-play-with.aspx (see the zip file at the end).
Even though a "DBCC CHECKDB (broken)" results in two errors with severity
level 16 the following code will never end up in the CATCH block..
BEGIN TRY
DBCC CHECKDB(broken)
END TRY
BEGIN CATCH
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
Does anyone know if this is by design or a bug? I'm using SQL Server 2005
with Service Pack 2.Hi Allan,
I've reported it on connect as a bug.
Use @.@.ERROR instead and don't use BEGIN TRY.
The CATCH is never reached, in fact - if you using BEGIN TRY and DBCC
CHECKDB fails then it stays in the BEGIN block and if you check @.@.ERROR its
actually 0!
Tony.
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Allan" <allan@.newsgroups.nospam> wrote in message
news:B2E2D4EC-7F2D-434C-9F0C-AEA328C57188@.microsoft.com...
> I'm having problems catching errors from DBCC CHECKDB even though the
> errors
> have a severity level above 10. I'm using the "broken" database created by
> Paul Randal,
> http://blogs.msdn.com/sqlserverstor...-play-with.aspx
> (see the zip file at the end).
> Even though a "DBCC CHECKDB (broken)" results in two errors with severity
> level 16 the following code will never end up in the CATCH block..
> BEGIN TRY
> DBCC CHECKDB(broken)
> END TRY
> BEGIN CATCH
> 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
> Does anyone know if this is by design or a bug? I'm using SQL Server 2005
> with Service Pack 2.

ERROR_STATE() Always = 0?

Below is the contents of a SPROC I have. I want to return the error
info in the catch block for it so I call RaiseError. But the
ERROR_STATE() always comes up as 0 which is ilegal since it must be
between 1-127 I guess. So I wrote a stupid if block to set it to 1 to
stop that error.

But I'd really rather not do this. Can anyone tell what's wrong? Why am
I not getting a correct return from ERROR_STATE()?

Thanx much:

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM WebUser2Role WHERE WebUserID = @.WebUserID

INSERT INTO WebUser2Role
SELECT value, @.WebUserID FROM fIntList2Table(@.RoleIDList)
END TRY
BEGIN CATCH
DECLARE @.ErrorMessage NVARCHAR(4000)
DECLARE @.ErrorSeverity INT
DECLARE @.ErrorState INT

SELECT
@.ErrorMessage = ERROR_MESSAGE(),
@.ErrorSeverity = ERROR_SEVERITY(),
@.ErrorState = ERROR_STATE();

IF @.ErrorState = 0
BEGIN
SET @.ErrorState = 1
END

IF @.@.TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END

RAISERROR (@.ErrorMessage, @.ErrorSeverity, @.ErrorState )

END CATCH

IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
ENDThe only thing I can think of which may help is that
Errors with a severity of 10 or lower are considered warnings or
informational messages, and are not handled by TRY.CATCH blocks.
therefore maybe it's not recognised as an error ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<wackyphill@.yahoo.com> wrote in message
news:1143771643.520517.319500@.e56g2000cwe.googlegr oups.com...
> Below is the contents of a SPROC I have. I want to return the error
> info in the catch block for it so I call RaiseError. But the
> ERROR_STATE() always comes up as 0 which is ilegal since it must be
> between 1-127 I guess. So I wrote a stupid if block to set it to 1 to
> stop that error.
> But I'd really rather not do this. Can anyone tell what's wrong? Why am
> I not getting a correct return from ERROR_STATE()?
> Thanx much:
>
>
> BEGIN TRY
> BEGIN TRANSACTION
> DELETE FROM WebUser2Role WHERE WebUserID = @.WebUserID
> INSERT INTO WebUser2Role
> SELECT value, @.WebUserID FROM fIntList2Table(@.RoleIDList)
> END TRY
> BEGIN CATCH
> DECLARE @.ErrorMessage NVARCHAR(4000)
> DECLARE @.ErrorSeverity INT
> DECLARE @.ErrorState INT
> SELECT
> @.ErrorMessage = ERROR_MESSAGE(),
> @.ErrorSeverity = ERROR_SEVERITY(),
> @.ErrorState = ERROR_STATE();
> IF @.ErrorState = 0
> BEGIN
> SET @.ErrorState = 1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> ROLLBACK TRANSACTION
> END
> RAISERROR (@.ErrorMessage, @.ErrorSeverity, @.ErrorState )
> END CATCH
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRANSACTION
> END|||No it deffinately is going into the catch block.

But ERROR_STATE() in the catch block returns 0. This poses a problem
when I call RaiseError because 0 is not a valid state.

error_line() not accurate?

Hello,

I'm just trying out this new try-catch stuff in sql server 2005....

Using the example from the help, "Using TRY... CATCH in Transact-SQL" , it shows how things are done in the adventure works database.. loggin the error to the errorlog table and all that... looks great.. but I notice that when I implement this code in my project.. and tested by putting in a line that causes a divide by zero error... that the line number reported by error_line is acutally not the line at which the divide by zero code resides....

Any suggestions as to what would put the error_line out of whack? I have comments and some string literals in the code..would they be throwing it?

Thanks

Try posting a sample here that others can recreate. That way we can address the issue specifically.

error_line() not accurate?

Hello,

I'm just trying out this new try-catch stuff in sql server 2005....

Using the example from the help, "Using TRY... CATCH in Transact-SQL" , it shows how things are done in the adventure works database.. loggin the error to the errorlog table and all that... looks great.. but I notice that when I implement this code in my project.. and tested by putting in a line that causes a divide by zero error... that the line number reported by error_line is acutally not the line at which the divide by zero code resides....

Any suggestions as to what would put the error_line out of whack? I have comments and some string literals in the code..would they be throwing it?

Thanks

Try posting a sample here that others can recreate. That way we can address the issue specifically.