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 Event Source: SQLSERVERAGENT Event Category: Job Engine Event ID: 208 Date: date Time: time User: NA Computer: servername Description: SQL Server Scheduled Job 'Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan3'' (0x385AEFD8A5406044BF854C7011E501AD) - Status: Failed - Invoked on: 2005-06-23 16:05:37 - Message: The job failed. The Job was invoked by User CORPA\Administrator. The last step to run was step 1 (Step 1). For more information, see Help and Support Center at http://support.microsoft.com.
Additionally, the following error message is logged in the Database Maintenance Plan History entry:
[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 when the database contains a table that has an index on a computed column.
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.
Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan name' For example, add the parameters to the command as shown in the following command: