Indexed Computed Column Update Error

• Richard Williams-416852

  Old Hand

  Points: 324

  More actions

  January 10, 2008 at 10:36 am

  #69695

  I have a very simple update on a table with a computed column which is also indexed.

  The update is not on the computed column (I've tried it on all columns with the same error) and other updates in other stored procedures and code do not have the same error.

  The error is the good old:

  UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, 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.

  I've tried all the normal stuff that can be found on this forum i.e:

  SET ANSI_NULLS ON

  SET ANSI_PADDING ON

  SET ANSI_WARNINGS ON

  SET ARITHABORT ON

  SET CONCAT_NULL_YIELDS_NULL ON

  SET QUOTED_IDENTIFIER ON

  SET NUMERIC_ROUNDABORT OFF

  in both the SP in question and database wide in the properties and still I get the error.

  Any update even if it's a literal update of a field not related to the indexed computed column brings about the same error.

  Does anyone have any ideas?

• Richard Williams-416852

  Old Hand

  Points: 324

  More actions

  January 10, 2008 at 10:39 am

  #766753

  p.s. the same update is fine if I run it in Management Studio just as a script - i.e. not in a stored procedure 😀

• Richard Williams-416852

  Old Hand

  Points: 324

  More actions

  January 10, 2008 at 10:43 am

  #766755

  Sorry,

  having written this it suddenly occurred to me that it might be the settings in action when the SP was created that counted to by doing :

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[spCERTNET_BRMSyncGlobalSegment]

  I appear to have solved my problem 🙂