0

When I run the following statement ALTER TABLE MyTable DROP COLUMN MyIdentityColumn I receive the following error message:

SELECT 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 filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Firstly I'm confused because I'm not running a select statement. The column is an identity column which might explain the reference to computed columns but there are no keys, constraints, triggers or indexes on this table (the table was created from another table with a select * from SourceTable into MyTable).

Secondly, what do I need to do to drop this column? Even deleting from the SSMS menu fails with the same message.

  • You have to recreate table with correct set options. You can follow schema switch approach - stackoverflow.com/a/10072304/1387418 – Kin Shah Nov 17 '15 at 12:54
  • This table was created with the correct set options. Both ANSI_NULLS and QUOTED_IDENTIFIER are on. The table has in this case been newly created. – Jamie Alford Dec 4 '15 at 3:03
1

Insert profanity of choice.

The problem was caused by a database scoped trigger. Disabling / rewriting the trigger resolved the issue.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.