SQL Server Performance

Update Statistics Failed

Discussion in 'Performance Tuning for DBAs' started by dtritt, Dec 10, 2004.

  1. dtritt New Member

    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
  2. dtritt New Member

    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
  3. thomas New Member

    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
  4. dtritt New Member

    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
  5. satya Moderator

  6. thomas New Member

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

    Tom Pullen
    DBA, Oxfam GB
  7. satya Moderator

    Thanks Tom, I was off from forum for couple of days due to a live project implementation... lately it was successful though... so relaxing a bit with a refresh in forum.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. dtritt New Member

    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
  9. satya Moderator

    As I said before if you've scheduled DBCC DBREINDEX jobs against this database, then intermittently you can run UPDATE STATS on required tables.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. pcoyne New Member

    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?
  11. satya Moderator

  12. pcoyne New Member

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

    So due to that option enabled previously the STATS were created on the SYSINDEXES table and contributed this issue.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. DJC New Member

    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?
  15. satya Moderator

  16. DJC New Member

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

  18. thisisfutile New Member

    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
  19. Luis Martin Moderator

    quote:Originally posted by thisisfutile


    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

    And?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  20. MohammedU New Member

    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
  21. tripanu New Member

    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
  22. Luis Martin Moderator

    Welcome to the forum!!!.Please next time read post date. You are answering 2 years ago post.Thanks,

Share This Page