none
how to turn on QUOTED_IDENTIFIER on SQLServer? RRS feed

  • 问题

  • [on behalf of Rick XU]

     

    I have a complex 3rd party SQL DB. I created a indexed view based a few tables in it, just for performance. As we know, the indexed view add such a constraint on the table: when inserting, updating and deleting rows in those tables, the ANSI_NULLS and QUOTED_IDENTIFIER settings must be ON, or we will get an error.

     

    Msg 1934, Level 16, State 1, Procedure Cat_DeleteBibliographicRecord, Line 85

    DELETE 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 filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

     

    Unfortunately, the DB has the QUOTED_IDENTIFIER settings off, and what's worse, all the stored procedures has QUOTED_IDENTIFIER off when created, which means when executing these stored procedures, the error will be thrown.

     

    The last solution I can think of is:

    Connection to SQL, set the ANSI_NULLS and QUOTED_IDENTIFIER on in the current session,

    then alter all the stored procedures to their original definition. That will change, maybe, thousands of stored procedures in this DB, which I think is very dangerous, 'cause a 3rd party C/S tool will access this DB for customers.

     

    Do you have any thoughts on how to just alter the stored procedure's metadata to change the QUOTED_IDENTIFIER to ON, without touching the definition of SP? Do you have any discussion list I can post this question to?

    2010年4月7日 2:52

答案

  • Not sure how you access that db, with your own code or third party's. With third party's code, you violated support agreement (if have one) already by adding indexed view in the db. If with your own code, you can create your own sps with ANSI_NULLS and QUOTED_IDENTIFIER on.

    2010年4月7日 3:13

全部回复

  • Not sure how you access that db, with your own code or third party's. With third party's code, you violated support agreement (if have one) already by adding indexed view in the db. If with your own code, you can create your own sps with ANSI_NULLS and QUOTED_IDENTIFIER on.

    2010年4月7日 3:13
  • That's what I want to avoid. Create indexed view based those table is just a read operation, better than altering something. And it is common that we use import sql script to import data into some other tables.

    So technically, is it possible not useing the 'alter procedure'?

     

    Rick

    2010年4月8日 8:11
  • Don't think so if you have to use existing sps.
    2010年4月8日 16:45