error rebuilding index help 
Author Message
 error rebuilding index help

i upgraded a server from 7 to 2000 (8.00.760) about 3 weeks ago.
i have a database with a table that has two computed columns in it.
nothing strange about the computed columns, they simply concatenate two
char(4) fields together.
i have a maintenance plan that rebuilds the indexes once a week.
i ran that after the upgrade no problems.
it ran the following weekend, no problems.
now, it won't rebuild the indexes on that table with the computed
column.
i get the following error.
[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'.

i found on microsoft's web site the following :
The connection on which the index is created, and all connections
attempting INSERT, UPDATE, or DELETE statements that will change values
in the index, must have six SET options set to ON and one option set to
OFF. The optimizer ignores an index on a computed column for any SELECT
statement executed by a connection that does not have these same option
settings.
These options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
In addition to these ON settings, the NUMERIC_ROUNDABORT option must be
set to OFF.

i can run the dbcc dbreindex command from query analyzer with the
correct settings as listed above and won't get the error.

even if i change all the settings for the server default connection
options to what that says, when the sqlagent executes this job, the
first thing it does is a "set quoted identifier off" which makes the
dbcc dbreindex give the same error as above minus the arithabort.

what can i do here?  i don't see any way to prevent the error when the
sqlagent runs the job.



Sat, 15 Oct 2005 13:50:07 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Difference in ranking results from a full index rebuild verses an incremental rebuild

2. rebuild or not rebuild index file, when ?, how?

3. Computed columns & rebuilding indexes ERROR

4. 1105 Error rebuilding Clustered Index

5. Error 169 when rebuilding clustered index

6. 170 error in db maintenance rebuild index

7. Error message from index rebuild

8. Rebuild index error

9. Oracle8 Context Cardridge Errors on Rebuild of Indexes

10. Oracle8 Context Cartridge error on rebuild of indexes

11. Help to Rebuild syscomments index

12. locking during index rebuild making db unavailable -- please help


 
Powered by phpBB® Forum Software