Post reply

ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

  • March 24, 2008 at 8:47 am

    #120645

    Hey all,

    Trying to find what index I blew up on my maintenance with this error:

    ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

    Can you query for computed columns? How about indexed views?

    Any help would be appreciated.

    Thanks

  • March 24, 2008 at 9:04 am

    #793045

    Check this link http://support.microsoft.com/kb/902388

    It is for SQL2000 SP4, not sure whether it applies to SQL2005.

    I had a similar problem in a SQL2000 maintenance plan (optimization), the workaround worked.

  • March 24, 2008 at 9:12 am

    #793051

    I don't use maintenance plans ...

    I think this may have to do with the index being created with quoted identifier on ... perhaps the computed column has a double quote value, I'm really not sure.

    I'd like to find out how to query for computed columns in a table so that I can attempt to find this table and see If I can recreate the issue.

  • March 24, 2008 at 9:39 am

    #793073

    SQL2005 Books online "Creating Indexes on Computed Columns" mentioned about the set option on/off.

    This link may help although it is for 2000. http://support.microsoft.com/kb/301292/

  • March 24, 2008 at 10:20 am

    #793103

    Found it ... is_computed in sys.columns.

  • March 24, 2008 at 10:27 am

    #793108

    Ok, I looked at each column that has a computed column, and none of them contain a double quote ... yet they all have single quotes for strings. I'm not yet sure I understand the problem at hand yet ... Pretty sure I can fix it by simply tossing a SET QUOTED_IDENTIFIER on at the top of my procedure, but I really want to understand why I'm having this issue first.

    edit: Actually, I just went and successfully rebuilt each of the indexes that their table had a computed column ... so I guess that was not my issue.

  • March 24, 2008 at 10:33 am

    #793112

    Good to know this column.

  • March 24, 2008 at 1:58 pm

    #793207

    From what I remember, there are a number of requirements for indexing computed columns, regardless of what's in the column or what it's called. Similar to the requirements for indexed views (but not as strict).

    Quoted identifiers is one of those requirements.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 26, 2010 at 2:17 pm

    #1125831

    Adam Bean (3/24/2008)

    Ok, I looked at each column that has a computed column, and none of them contain a double quote ... yet they all have single quotes for strings. I'm not yet sure I understand the problem at hand yet ... Pretty sure I can fix it by simply tossing a SET QUOTED_IDENTIFIER on at the top of my procedure, but I really want to understand why I'm having this issue first.

    edit: Actually, I just went and successfully rebuilt each of the indexes that their table had a computed column ... so I guess that was not my issue.

    This is probably long resolved, although finding this thread didn't help me. To help anyone else who comes across this; this can happen if the proc you're using has the quoted_identifier option set incorrectly. Not necessarily your proc or script, but for example if you're using sp_MSforeachtable to execute this command for each table it won't be set correctly - even if you set quoted_identfier on. I recently ran into this with SQL Server 2008.


    -Ken

  • May 21, 2010 at 9:51 am

    #1170928

    spongemagnet (2/26/2010)

    Adam Bean (3/24/2008)

    if you're using sp_MSforeachtable to execute this command for each table it won't be set correctly - even if you set quoted_identfier on. I recently ran into this with SQL Server 2008.

    Hi-

    I am having this problem when I use sp_msforeachtable to rebuild indexes. How did you get around it if setting quoted_identifier doesn't have any impact?

    Thanks

  • July 13, 2010 at 12:49 pm

    #1192289

    Hi-

    I am having this problem when I use sp_msforeachtable to rebuild indexes. How did you get around it if setting quoted_identifier doesn't have any impact?

    Thanks

    For a different reason I ended up needing to put some intelligence in my reindexing script (reindexing a large mirrored database.) I wrote a script to loop through sys.tables, sys.indexes, and sys.dm_db_index_physical_stats to reindex or rebuild based on fragmentation percentage. I use this now on all my reindexing jobs on any 2008 servers. My older servers I leave the sp_msforeachtable unless there's a problem.


    -Ken

  • April 13, 2011 at 12:30 pm

    #1311247

    I had the same problem but only on certain database - all of them had the tables defined and created the same way but they had different data. My errors were showing up because of a Computed Column.

    However, SET QUOTED_IDENTIFIER ON worked for me in my sp_MSforeachtable command to defrag all indexes by added it inside the same nvarchar as the ALTER INDEX like this:

    EXEC sp_MSforeachtable @command1='print ''?''', @command2='set QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80,ONLINE=OFF)'

  • February 20, 2012 at 10:41 am

    #1449334

    A late reply but the solution from Michael Rybicki worked perfectly for me as I was experiencing the exact same error on a large OLTP database on SQL 2008 R2.

  • October 5, 2012 at 11:35 am

    #1546235

    If you are using sp_MSforeachtable, then you have to add the set command in the command. Like this:

    EXEC sp_MSforeachtable @command1="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ? REBUILD;"

    Putting it before the exec does not fix the issue.

Viewing 14 posts - 1 through 13 (of 13 total)

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