Article ID: 902388 - View products that this article applies to.
When you try to use the Database Maintenance Plan Wizard to create a maintenance plan in Microsoft SQL Server 2000, an event that is similar to the following event may be logged in the Application log:
Event Type: Warning
This problem occurs when the database contains a table that has an index on a computed column.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
This problem occurs because a job is created after you create a maintenance plan by using the Database Maintenance Plan Wizard. This job has the following name:
Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan name'This job contains a dbo.xp_sqlmaint stored procedure. This stored procedure calls the Sqlmaint utility. The Sqlmaint utility runs DBCC checks, backs up a database and the database transaction log, updates statistics, and rebuilds indexes. Then, the Sqlmaint utility runs the DBCC CHECKDB statement, the DBCC DBREINDEX statement, and the DBCC CHECKTABLE statement. These statements require that the QUOTED_IDENTIFIER SET option is set to ON. By default, the Database Maintenance Plan Wizard sets the QUOTED_IDENTIFIER SET option to ON when the Database Maintenance Plan Wizard creates this job.
Additionally, when the QUOTED_IDENTIFIER SET option is set to ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. However, the SQL statement in the command that is included in the first step of this job only uses single quotation marks. The identifier in this SQL statement is delimited by single quotation marks instead of by double quotation marks. Therefore, this problem occurs.
Note By default, the QUOTED_IDENTIFIER SET option is set to OFF. When the QUOTED_IDENTIFIER SET option is set to OFF, identifiers cannot be delimited by double quotation marks. When the QUOTED_IDENTIFIER SET option is set to OFF, identifiers must follow all Transact-SQL rules for identifiers.
To work around this problem, add the –S ServerName\InstanceName parameter and the -SupportComputedColumn parameter to the command that is included in the first step of the following jobs.
Note If you are using a named instance, the placeholder ServerName\InstanceName represents the server name and the instance name. If you are using a default instance of SQL Server 2000, you can use the –S ServerName parameter, or you can safely ignore the parameter.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/301292/ )SET OPTION considerations when running DBCC with indexes on computed columns
Article ID: 902388 - Last Review: November 2, 2007 - Revision: 4.2
Contact us for more help
Connect with Answer Desk for expert help.