Post reply

QUOTED_IDENTIFIER Error When Running an ALTER INDEX Job

  • November 26, 2007 at 3:01 am

    #153979

    I'm running the following from within a job:

    sp_MSForEachTable "ALTER INDEX ALL ON ? REBUILD"

    This works for other databases I have but on one database I get the following error after 15 minutes:

    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. [SQLSTATE 42000] (Error 1934).

    Does anyone know what I can do to get this to work, I tried to put SET QUOTED_IDENTIFIER ON into the job but it still failed on the same thing and then I tried to set it off but it still failed.

    I wrote the output to a text file but it still told me no more than the above error message.

    Thanks

  • November 26, 2007 at 3:12 am

    #753905

    Check your indexed views and whether they were created with ansi nulls and quoted identifiers set to on.

    You can get this information from sys.sql_modules (on 2005) or by using the OBJECTPROPERTY function with 'ExecIsAnsiNullsOn' and 'ExecIsQuotedIdentOn'

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 26, 2007 at 3:47 am

    #753915

    hi Busxton 67,

    The following is from BOL:

    SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views .

    Regards,

    Ahmed

  • November 26, 2007 at 3:51 am

    #753916

    Sorry

    I missed to tell you When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled

    Regards,

    Ahmed

  • November 26, 2007 at 4:38 am

    #753939

    It's not the views, sp_MSforEachTable only works on tables.

    I don't think it's the way that the indexes were created although that is very interesting to know, I think it's the sp_MSforEachTable stored procedure, I just ran the ALTER INDEX command individually for each index in the database and it worked fine, I don't know why this works for some databases and not others but that's probably why Microsoft chose not to document it.

    Thanks anyway, I think I might have to go back to the self written iterate.

  • November 26, 2007 at 4:54 am

    #753949

    Hi Buxton69

    Check the following code http://www.mssqltips.com/tip.asp?tip=1367 (good one)

    regards,

    Ahmed

  • November 26, 2007 at 4:56 am

    #753950

    hi !,

    Try this once.

    EXECUTE sp_msforeachtable 'print "?" dbcc DBREINDEX ("?")'

  • November 26, 2007 at 8:57 am

    #754107

    Ahmed Bouzamondo (11/26/2007)

    Hi Buxton69

    Check the following code http://www.mssqltips.com/tip.asp?tip=1367 (good one)

    regards,

    Ahmed

    Ahmed, thanks for the link, I think I'll use that, it looks like someones just adopted the Microsoft style, if you look at the code for sp_MSforEachTable they are very similar.

    Abhijit, according to BOL DEREINDEX shouldn't be used anymore, printing out the commands would not be much good to me from within a job.

  • November 26, 2007 at 2:54 pm

    #754286

    Buxton69 (11/26/2007)

    It's not the views, sp_MSforEachTable only works on tables.

    I don't think it's the way that the indexes were created although that is very interesting to know, I think it's the sp_MSforEachTable stored procedure, I just ran the ALTER INDEX command individually for each index in the database and it worked fine, I don't know why this works for some databases and not others but that's probably why Microsoft chose not to document it.

    Thanks anyway, I think I might have to go back to the self written iterate.

    INDEXED VIEWS are schema bounded to the underlying tables. Therefore when you touch the tables the "indexed" views could "potentially" be touched too. Definitely COMPUTED COLUMNS that are persisted also fall on this category and BOTH require those ANSI Settings 😀


    * Noel

Viewing 9 posts - 1 through 8 (of 8 total)

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