February 17, 2012 at 8:47 am
I have simple query:
set quoted_identifier off
BEGIN tran
UPDATE DW.Table1
SET Key = 5
ROLLBACK;
I get this error message:
Msg 1934, Level 16, State 1, Line 5
UPDATE 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.
If I set it to ON, it runs fine.
This is a table, not a view. And it does not have computed columns or filetered indexes.
February 17, 2012 at 9:13 am
SQL Guy 1 (2/17/2012)
I have simple query:set quoted_identifier off
BEGIN tran
UPDATE DW.Table1
SET Key = 5
ROLLBACK;
I get this error message:
Msg 1934, Level 16, State 1, Line 5
UPDATE 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.
If I set it to ON, it runs fine.
This is a table, not a view. And it does not have computed columns or filetered indexes.
Is there a question here?
I would ask why do you want to turn QUOTED_IDENTIFIER OFF for this update anyway?
The reason you are getting the error is because you used a reserved word as a column name (Key).
From bol:
SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2012 at 2:07 pm
You're also safer doing it like this, note the brackets.
BEGIN tran
UPDATE DW.Table1
SET [Key] = 5
ROLLBACK;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply