Microsoft KB Archive/301292

= SET OPTION considerations when running DBCC with indexes on computed columns =

Article ID: 301292

Article Last Modified on 12/20/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q301292



SYMPTOMS
DBCC CHECKTABLE, DBCC DBREINDEX, and DBCC CHECKDB may fail with the following error message if the database contains a table that has an index on a computed column:

DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.



MORE INFORMATION
This behavior is by design.

DBCC CHECKDB, DBREINDEX, and CHECKTABLE require the following SET options if an index on a computed column exists in the database or table:
 * ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON.
 * NUMERIC_ROUNDABORT must be set to OFF.

The error message is more likely to occur if the CHECKDB or CHECKTABLE is being scheduled from a SQL Server Agent job or from an Integrity Check in a Database Maintenance Plan. This is because by default the SQL Server Agent does not set QUOTED_IDENTIFIER or ARITHABORT. To schedule a DBCC CHECKTABLE or CHECKDB integrity check on the database, you must create a SQL Server Agent Job and in the Transact-SQL command you must add the needed SET OPTIONS as in the following example. SET ARITHABORT ON SET QUOTED_IDENTIFIER ON DBCC CHECKTABLE(mytable) go Here is a sample script describing how to run DBCC DBREINDEX on the entire database. SET ARITHABORT ON SET QUOTED_IDENTIFIER ON use MyDatabase -- CHANGE THE DATABASE NAME go declare @tabname sysname declare @dbstring varchar(300) declare @exec_string varchar(300) declare tabDBCC cursor for select table_name from information_schema.tables where table_type = 'base table' open tabDBCC fetch next from tabDBCC into @tabname select @dbstring = DB_NAME print 'Starting DBCC DBREINDEX for database ' + upper(@dbstring) while (@@fetch_status = 0) begin print 'Reindexing table ' + upper(@tabname) select @exec_string = 'dbcc dbreindex ([' + @tabname + '])' exec(@exec_string) fetch next from tabDBCC into @tabname end close tabDBCC deallocate tabDBCC print 'Finished DBCC DBREINDEX for database ' + upper(@dbstring) go For more information, click the following article number to view the article in the Microsoft Knowledge Base:

902388 Event ID: 208 may be logged, and a &quot;DBCC failed&quot; error message is logged when you try to use the Database Maintenance Plan Wizard to create a maintenance plan in SQL Server 2000

Keywords: kbprb KB301292

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.