Today I was running below query to rebuild all indexes:
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'
But it failed and shown below error:
Msg 1934, Level 16, State 1, Line 2
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 filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I searched for this on net and got some below solution:
EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
It means you need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'
But it failed and shown below error:
Msg 1934, Level 16, State 1, Line 2
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 filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
I searched for this on net and got some below solution:
EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'
It means you need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.
nice..post
ReplyDelete