Update Statistics Failed

Hello all,
We're running SQL Server 2000 & I'm running into the following error that I hope can be fixed easily. It reads the following:
[Microsoft SQL-DMO (ODBC SQLState:42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
STATISTICS failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.

The ODBC settings are set such that both the following are checked:
- Use ANSI quoted identifiers.
- Use ANSI nulls, paddings and warnings.

I checked Microsoft's site, and it describes the error, but it fails to mention how to fix this.
Does anyone have any ideas how to resolve this issue?

Many Thanks,
Dori

Hello All,
I left out a piece that I thought might be important.
The Statistics are automatically being run from a Maintenance Plan job that gets kicked off nightly.

Again... Thank you,
Dori

I've seen this before but with a dbcc checkdb() job. I can't recall all the details right now but I'm sure I fixed it by simply preceding the step with (in TSQL), SET QUOTED_IDENTIFIER OFF.

Tom Pullen
DBA, Oxfam GB

Tom,
Thanks for the input. I opened the Jobs portion from the SQL Server Agent tree in Enterprise Manager and added a preceeding step so that SET QUOTED_IDENTIFIER OFF. Still no luck though and I still get the same error when kicking off this Integrity Check Job. Does the "SET QUOTED_IDENTIFIER OFF" have to be in the same step as the Integrity Check's TSQL?

Thanks,
Dori

That's the one! I KNEW Satya's link machine would soon swing into action!

Tom Pullen
DBA, Oxfam GB

Tom & Satya,
Thanks so much! We had to manually create a job with SET options as the first step, and then run statistics "manually" in the second step.

Thanks again!
Dori

I had this same problem in a maintenance job to rebuild indexes. The table that it was failing on did have a computed column but no indexes were created on this column. Using SET ARITHABORT ON and SET QUOTED_IDENTIFIER ON had no effect. We had tried everything and had just about given up and were going to manually run DBCC DBREINDEX periodically. I then noticed, in sysindexes, that there were statistics created against the computed column. I thought, it couldn't be that easy. I dropped the statistics and the problem went away. Can anyone explain why Sql viewed these statistics as an index against the column?

Not currently but it may have been in place sometime in the past. I haven't always managed these servers.

I have the same problem on a maintenance job attempting to rebuild indexes. I have a table that has three computed column's. No indexes exist on these columns and the SET ARITHABORT ON and SET QUOTED_IDENTIFIER ON does not make a difference. When I did a DBCC SHOW_STATISTICS on one of the computed column's it did have statistics, so I attempted to DROP STATISTICS on that column but got the message 'Cannot drop the statistics ' ' because it does not exist in the system catalog' Is the is the right direction or am I in left field?

THANKS! It works like a charm. [<img src='/community/emoticons/emotion-2.gif' alt='' />]

quote:Originally posted by dtritt

Hello all,
We're running SQL Server 2000 & I'm running into the following error that I hope can be fixed easily. It reads the following:
[Microsoft SQL-DMO (ODBC SQLState:42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
STATISTICS failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'.

The ODBC settings are set such that both the following are checked:
- Use ANSI quoted identifiers.
- Use ANSI nulls, paddings and warnings.

I checked Microsoft's site, and it describes the error, but it fails to mention how to fix this.
Does anyone have any ideas how to resolve this issue?

Many Thanks,
Dori

Do you have any indexes computed columns or indexed views?

Set the following options before running update stats against indexes computed columns or indexed views...


SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET ARITHABORT ON

Check the following article...
http://support.microsoft.com/kb/301292


MohammedU.
Moderator
SQL-Server-Performance.com

Hi,
I have gone through the error what I understand that the optimization job is falling which is assosiated with maintenace plan.
Could you please add some parameter to command line after -RebldIdx 10 inexisting SQL agent job as shown below:
EXECUTE master.dbo.xp_sqlmaint N'-S ServerNameInstanceName -PlanID <GUID> -WriteHistory -RebldIdx 10 -SupportComputedColumn'
Please let me know the result.
Thanks,
Anurag Tripathi