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.

Improve this question
2
  • You have to recreate table with correct set options. You can follow schema switch approach - stackoverflow.com/a/10072304/1387418
    – Kin Shah
    Nov 17, 2015 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, 2015 at 3:03

1 Answer 1

Reset to default
1

Insert profanity of choice.

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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