You are currently offline, waiting for your internet to reconnect

SET OPTION considerations when running DBCC with indexes on computed columns

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 ONSET QUOTED_IDENTIFIER ONDBCC 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 NAMEgo 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 "DBCC failed" error message is logged when you try to use the Database Maintenance Plan Wizard to create a maintenance plan in SQL Server 2000
Properties

Article ID: 301292 - Last Review: 12/20/2005 23:47:27 - Revision: 5.3

  • Microsoft SQL Server 2000 Standard Edition
  • kbprb KB301292
Feedback