SQL State 42000 Rebuild Index

So when using this script

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

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s