Indexed Computed Column Update Error

  • 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?

  • 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 😀

  • 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 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply