Forum Discussion

buaykiliao's avatar
buaykiliao
Level 2
16 years ago
Solved

migrating to SQL 2005

Hi All,

Our environment have EV, DA and CA, all the databases resided in SQL 2000 server. We been told to migrate all databases to SQL 2005 on a new hardware. Found an article regards to optimizing SQL 2005 http://seer.support.veritas.com/docs/281261.htm

 

We execute the command on DEV environment EV database without a problem however encounter error message as below in DA and CA database. 

Msg 1934, Level 16, State 1, Line 1
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.

 

Just wonder whether there is a different command for DA and CA SQL database optimization or etc. 

 

Thanks in advance. 

   

 

  •  

    First you can  determine what the current value is:


    SELECT DATABASEPROPERTY(‘Databasename', ‘IsQuotedIdentifiersEnabled’)

    --0 = Off
    --1 = On

    --Step 2: Change the Quoted Identifier:

    EXEC sp_dboption ‘Databasename’, ‘Quoted_Identifier’, ‘OFF’

    --or 'ON'


    here is a reference msft kb:
    http://support.microsoft.com/kb/301292
  •  

    First you can  determine what the current value is:


    SELECT DATABASEPROPERTY(‘Databasename', ‘IsQuotedIdentifiersEnabled’)

    --0 = Off
    --1 = On

    --Step 2: Change the Quoted Identifier:

    EXEC sp_dboption ‘Databasename’, ‘Quoted_Identifier’, ‘OFF’

    --or 'ON'


    here is a reference msft kb:
    http://support.microsoft.com/kb/301292