Update stats error

[Luvsql]

I ran update stats and received the following error:

Executed as user: SQL-01\Administrator. UPDATE STATISTICS failed because the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
ARITHABORT'. [SQLSTATE 42000] (Error 1934) Associated statement is not
prepared [SQLSTATE HY007] (Error 0). The step failed.

It doesn't say which table and what the SET options need to be set to. Any thoughts?

 

[mrdenny]

In Query Analyzer then are defaulted to on, so you'll want to off. If you have any tables with computed columns, those would be the ones that are causing the problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luvsql]

This is run as a job, so is there a way to turn it off during the job. I unfortunately, did not create any of the tables, so I am unsure if any tables have computed values.

 

[mrdenny]

Are you running this via a maintenance plan?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

When little to know activity is happening on the db (weekend or evening), fire up Profiler, set up a trace and then run the job. See what the job itself is doing. When it dies, you should have what query was doing something to what table in the last few lines of the trace record.

This will help you track down the error to table and job step.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Luvsql]

The error was actually on the rebuild:

Rebuilding indexes for table 'xyz'
[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'.

The table has 37 rows with an example of data. How can we change the set options on a table? The default value of the key is (getdate())

 

[mrdenny]

Are you running this via a maintenance plan or are you using T/SQL to rebuild the indexes?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luvsql]

It's via a maintenance plan and I also tried running the index with a tsql job.

 

[Luvsql]

There is only one table that the reindex is failing on. With the job created using the db maintenance wizard, can we exclude one table from the maintenance?

 

[Catadmin]

I don't see a way to exclude tables on SQL 2000. However, on SQL 2005, modify the Rebuild Index Task and change the Object to "Table" instead of "Tables and Views". Then you can choose between what tables you want to rebuild indexes on.

If you want to rebuild views, you'll have to add a separate task for that.

Hope this helps.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

That's why it's failing. It's a known bug with the maintaince plans that they can't reindex tables that have computed columns in them.

Check the table that is failing and see if there are any computed columns.

When SQLmaint connects it uses hard coded settings to connect that can't be changed. Because of this some times you can't use the maint plan to handle the tables, and you need to write your own via T/SQL for those tables/databases.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com