SQL State 42000 Rebuild Index

So when using this script

USE [database name]
GO
exec sp_MSForEachtable @command1=”ALTER INDEX ALL ON ? REBUILD”
GO
exec sp_MSForEachtable @command1=”UPDATE STATISTICS ? WITH FULLSCAN”
GO

I started to receive this error message:

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]

When you try to rebuild a indexed view or a computed column you will run into this error. I quickly tried adding a SET QUOTED_IDENTIFIER ON to the top of my query but after some reading found out that this has no impact when using the sp_MSForEachTable.

For now I created a DB Maintenance Plan to rebuild the indexes and recompute the statistics. Another way around the issue would be to build a custom script/stored procedure to loop through all the tables in sys.objects (where type = ‘U’ and then using the SET statement before each ALTER INDEX statement.

Advertisements