Quoted identifier setting

Giganews Newsgroups
Subject: Quoted identifier setting
Posted by:  simon (zupan.n…@gmail.com)
Date: Tue, 29 Mar 2011

I have default setting for my sql2008 server:
sp_configure 'user options', 6008

I think it's the best default setting, because you don't have any
troubles with filtered index, indexed views,...

This setting means that all connections to this server will have
option 6008. (It includes also that QUOTED IDENTIFIER are ON.)
Ok.

Now I execute one  simple query in SSMS:
SELECT * from myTable WHERE col1=5
(col1 is filtered index)

It works.
Now I copy this query into sql server job and execute it with the same
user.
I get the following error:

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. [SQLSTATE 42000] (Error
1934).  The step failed.

Why I'm getting this error if quoted identifier is setted to on for my
connection?

I found out that there is also database option: Quoted identifiers
enabled. What is this for? I thought that quoted identifier setting is
only on connection level?
But no matter of this setting, the error in my job is still there.

Only if I explicitly put this statement before my query:
SET QUOTED_IDENTIFIER ON

the error is gone.

I would like to understand, why?

Thank you,
Simon

Replies