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.
Best Answer
Insert profanity of choice.
The problem was caused by a database scoped trigger. Disabling / rewriting the trigger resolved the issue.