Post reply

Maintenance error

  • July 30, 2004 at 4:04 am

    #86266

    Every month I do maintenance my SQL servers and recently I had a consultant come in and make a change to one of the applications they sold and now when I do the Optimization and Integrity checks 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'.

    Can anyone shed some light on this.  Everything was working fine before. I've deleted and re-done the maintenance plan.  Help me please

    Thanks,

    Kris

  • July 30, 2004 at 12:03 pm

    #517088

    You don't say what DBCC checks you're doing but try this in your maintenance plan (example uses CHECKDB):

    SET QUOTED_IDENTIFIER ON

    DBCC CHECKDB(mydb)

     

     

    Greg

  • July 30, 2004 at 7:43 pm

    #517120

    All I'm doing is the optimisation checks through EM.  It's just a maintenance plan set up through the GUI.  I ran that script but I'm not sure what I should be looking for, because I got the same error as before.  I have also ticked and unticked "use quoted identifiers" and still get the dame error.

    Thanks,

    Kris

  • August 1, 2004 at 10:28 pm

    #517169

    The consultant wasn't touching indexes, per chance? There is a known issue with putting indexes on computed columns. Greg has the right solution. Microsoft describes this behavior as "by design" and I've run into it myself. Here's a KB article describing the issue. As you can see, MS's recommendation is exactly what Greg has suggested.

    PRB: SET OPTION Considerations When Running DBCC with Indexes On Computed Columns

    K. Brian Kelley
    @kbriankelley

  • August 4, 2004 at 9:46 pm

    #517743

    Thanks, that seemed to have worked.

    Thanks,

    Kris

  • August 24, 2004 at 8:29 am

    #520330

    Then again, I have just now finished correcting this error by rebuilding the offending table.  I had been getting this error on a test database that had a table using a computed column, but the computed column wasn't indexed.  Other databases substantially similar to this (one being another test version of the database, another the production copy) had the same table with a computed column, but they weren't getting the error.  There was no correlation between whether I had Arithabort or quoted_identifiers on or off, singly or together.

    So I rebuilt and repopulated the stupid table, and now my database optimization job works without error, and without turning arithabort, quoted_identifiers, or any of the other settings related to this issue.  Go figure.

    - Patrick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply