Error rebuilding index on indexed view

• Ken Davis

  SSCarpal Tunnel

  Points: 4329

  More actions

  September 22, 2011 at 1:56 pm

  #243622

  I am trying to rebuild the index on an indexed view in a job. The following runs successfully when I run it in a query window, connected as myself:

  ALTER INDEX ix1

  ON db1.dbo.view1

  REBUILD WITH (ONLINE = ON)

  But in a job step it fails with:

  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] (Error 1934). The step failed.

  Setting quoted_identifier ON before the alter index and setting it off afterwards does not fix the problem. What would cause this?

  thanks

• Chuck Hottle

  SSCarpal Tunnel

  Points: 4777

  More actions

  September 22, 2011 at 4:16 pm

  #1385484

  There are a number of links in this post that may be applicable.

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117683

• Panders

  Right there with Babe

  Points: 772

  More actions

  September 23, 2011 at 3:04 am

  #1385586

  Try adding this before you rebuild your index. (script out the index from the view and use these set options!)

  SET ARITHABORT ON

  GO

  SET CONCAT_NULL_YIELDS_NULL ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO

  SET ANSI_PADDING ON

  GO

  SET ANSI_WARNINGS ON

  GO

  SET NUMERIC_ROUNDABORT OFF

  GO

• Ken Davis

  SSCarpal Tunnel

  Points: 4329

  More actions

  October 5, 2011 at 11:45 am

  #1390862

  Thanks for the replies. I got it to work in a job with:

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER INDEX xxxxxxx

  ON yyyyyyyy

  REBUILD WITH (ONLINE = ON)

  Could have sworn I tried that before, but I guess not!