1

I have a stored procedure that updates the source column used in a computed column.

That stored procedure has SET ANSI_WARNINGS OFF;.

When the procedure executes, and tries to update the source column, the following error message is received:

Msg 1934, Level 16, State 1, Procedure my_PROC, Line XX UPDATE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The error stops when either:

  1. We stop turning ANSI_WARNINGS off, or;
  2. The source column (the one used in deriving the computed column) is removed from the update statement.

There is one clustered index and one non-clustered index, both on columns which are not involved in any way in the computed column definition.

What is it about the column being used in a computed column that causes this error?

  • Do you have a filtered index on that table? – SqlWorldWide Dec 8 '17 at 15:08
  • No, there is a clustered index on a column which is not involved in any way in the computed column definition, and 1 non-clustered index, also not involved in the computed column in any way. I will add this info to the question – GWR Dec 8 '17 at 15:24
  • 1
    Do you have any indexed views pointing to this table? – Scott Hodgin Dec 8 '17 at 15:32
  • 2
    This is exactly what the documentation says. – Erik Darling Dec 8 '17 at 15:40
  • Computed column created as persisted? Would cause this behaviour. It's not in the error message but is in the documentation as per Erik's link. – Gareth Lyons Dec 8 '17 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.