Error Handling in SQL

Hello Group,
I need help on handling an error. I am trying to handle errors raised by
DBCC DBREINDEX. To force a test I have SET ARITHABORT OFF and SET
QUOTED_IDENTIFIER OFF and issued the statement against a table with a
calculated column.
That raised the "DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'." error.
My problem is that procedure exits after this error and no statement is
executed after it. I cannot catch it, check its number or continue on the
procedure. How can I do that?
Thanks!
Giovanni BassiAs the batch terminates, there's not much you can do at the TSQL level when you've hit the error. So
you need to prevent it. DBCC USEROPTIONS will show you the value for these options.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Giovanni Bassi" <gbassi@.coair.com> wrote in message news:%234sQc0frDHA.2556@.TK2MSFTNGP09.phx.gbl...
> Hello Group,
> I need help on handling an error. I am trying to handle errors raised by
> DBCC DBREINDEX. To force a test I have SET ARITHABORT OFF and SET
> QUOTED_IDENTIFIER OFF and issued the statement against a table with a
> calculated column.
> That raised the "DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'." error.
> My problem is that procedure exits after this error and no statement is
> executed after it. I cannot catch it, check its number or continue on the
> procedure. How can I do that?
> Thanks!
> Giovanni Bassi
>

Error handling in cursor

I have cursor that loops through some records and calls a stored procedure. Sometimes the stored procedure raises an error. When an error is raised the cursor stops looping.

I would like the cursor to loop to the next record and continue executing the stored procedure. How do I implement this functionality? I suspect it has to do with using @.@.Error but I have limited experience with it. Thanks in advance.

Code Snippet

DECLARE PKIDs_Cursor CURSOR FOR

SELECT Id as PKID FROM Table1

OPEN PKIDs_Cursor;

DECLARE @.PKs VARCHAR(20)

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC dbo.spProcessInfo @.PKs, NULL;

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

END;

CLOSE PKIDs_Cursor;

DEALLOCATE PKIDs_Cursor;

By default, SQL Server follows “ON ERROR RESUME NEXT”, that means when error occurs (if it is not critical error) it allows continuing to execute the next statement. Considering this statement your loop won’t break. You need not to use the @.@.ERROR here. If you want to force to discontinue the loop then only you need this variable.

Note:

In SQL Server 2000 you can’t suppers the error message thrown by the server. But the execution will continue.

If you use SQL Server 2005 then you can use the TRY..CATCH to suppress the error messages.

If you provide the error message it will be more helpful.

|||

Thank you for the reply Manivannan. I am using SQL Server 2000. The error message is a custom message raised by the stored procedure:

-2 Warning: Mapping failed - No actions taken.

“ON ERROR RESUME NEXT” behavior does not appear to be in effect. Any ideas?|||

What Severity Level you used on your Raiserror? Severity Level might affect the work-flow of your batch.