SQLTeam.com | Weblogs | Forums

ALTER TABLE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'


#1

## Here is my script to alter a column in table:-

USE [HIS-MGMT-Subset]
GO

BEGIN TRANSACTION

DECLARE @ER int

ALTER TABLE [objects]
ALTER COLUMN ISBN nvarchar(450);

Select @ER= @@error
if @ER <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION

GO
--ROLLBACK TRANSACTION

## Error ouput:-

ALTER TABLE failed because the following SET options have incorrect settings: '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.

Please let me know what I need to do to execute the script


#2

You've probably got a Computed Column in that table. Need to SET QUOTED_IDENTIFIER appropriately for that (and IME I've had similar trouble with any VIEWs on that table that have indexes, or somesuch that I have now forgotten). It may be irrational, but we avoid computed columns in tables for that reason


#3

You should always have these settings in place when acting against any SQL object:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE ...