Quoted-Identifyer

Hi
Since short time I am experiencing the following problem when testing a
stored procedure.
Error on Insert since the following set options are not set properly:
QUOTED_IDENTIFYER
Sorry, that I do not have an exact error message, since I am working on
a german server, and I only translated the message by myself.
The error occured without changing the procedure or the data. It
suddenly appeared, then disappeared and then appeared again...
regards
StephanzDo any of your statements use "" around column names or aliases or values?
If a column name has spaces or is a reserved word, use [brackets] as opposed
to "double quotes" -- and strings should be delimited using 'single quotes'
rather than "double quotes".
"Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
news:uStQuCoyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi
> Since short time I am experiencing the following problem when testing a
> stored procedure.
> Error on Insert since the following set options are not set properly:
> QUOTED_IDENTIFYER
> Sorry, that I do not have an exact error message, since I am working on a
> german server, and I only translated the message by myself.
> The error occured without changing the procedure or the data. It suddenly
> appeared, then disappeared and then appeared again...
> regards
> Stephanz|||In all my Procedures there is no " (Double Quote).
I already checked this. No column name has any space or uses reserved
words... and all my strings are sourrounded with single quotes. That's
why I contacted this news group, because this behaviour seems to be kind
of strange.
Last w the procedure worked fine! This w, suddenly I got the error
and I put a "SET QUOTED_IDENTIFYER ON" right above the insert statement.
Then it worked. Today it stopped working and I moved the "SET
QUOTED_IDENTIFYER ON" statement right to the beginning of the stored
procedure and then it worked again. But I didn't change any column names
in the database nor did I change the stored procedure. That all
sometimes makes me even believe that software can be indeterministic
allthough I know that it isn't...
regards
Stephan
Aaron Bertrand [SQL Server MVP] wrote:
> Do any of your statements use "" around column names or aliases or values?
> If a column name has spaces or is a reserved word, use [brackets] as opposed
> to "double quotes" -- and strings should be delimited using 'single quotes
'
> rather than "double quotes".
>
>
> "Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
> news:uStQuCoyFHA.3588@.tk2msftngp13.phx.gbl...
>
>
>|||debug the procedure with print statments and the profiler,
I always put this at the beginning an end of my sProcs
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
...do stuff
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do any of your statements use "" around column names or aliases or values?
> If a column name has spaces or is a reserved word, use [brackets] as opposed
> to "double quotes" -- and strings should be delimited using 'single quotes
'
> rather than "double quotes".
>
>
> "Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
> news:uStQuCoyFHA.3588@.tk2msftngp13.phx.gbl...
>
>|||I traced down the problem a little bit:
The problem first arised when changing to a new developement server
which is hosted on a virtual test machine rather than on my old crappy
laptop ;-)
When creating tables, stored procedures and so on I never cared about
the QUOTED-IDENTIFYER options and I now had a look in the old version of
the database on my laptop and there for each stored procedure the
QUOTED-IDENTIFYER option was set to ON. Now (with exactly the same
create scripts for the stored procedures) for each procedure the server
sets this option to OFF. This is my problem now. How does the server
determine wheter to set or unset the QUOTED-IDENTIFYER option when
creating stored procedures.
btw... The error on the insert statement happened even though I did not
have any double quotes or similar constructs. SQL server requires that
when performing inserts, deletes, updates and creates on tables with
indexed views QUOTED-IDENTIFYERS must be set to ON. Otherwise those
opererations will fail...
Stephan Zaubzer wrote:
> In all my Procedures there is no " (Double Quote).
> I already checked this. No column name has any space or uses reserved
> words... and all my strings are sourrounded with single quotes. That's
> why I contacted this news group, because this behaviour seems to be kind
> of strange.
> Last w the procedure worked fine! This w, suddenly I got the error
> and I put a "SET QUOTED_IDENTIFYER ON" right above the insert statement.
> Then it worked. Today it stopped working and I moved the "SET
> QUOTED_IDENTIFYER ON" statement right to the beginning of the stored
> procedure and then it worked again. But I didn't change any column names
> in the database nor did I change the stored procedure. That all
> sometimes makes me even believe that software can be indeterministic
> allthough I know that it isn't...
> regards
> Stephan
> Aaron Bertrand [SQL Server MVP] wrote:
>|||> sets this option to OFF. This is my problem now. How does the server
> determine wheter to set or unset the QUOTED-IDENTIFYER option when
> creating stored procedures.
The server doesn't. The connection settings in effect for the dbms
connection used to edit the procedure are used (and saved). You determine
these settings by the method (or application) you use to edit the procedure.
You should read the information in BOL under the topic of "create
procedure". There is a detailed explanation of the settings that are
relevant to procedure creation and execution - very important information!

> btw... The error on the insert statement happened even though I did not
> have any double quotes or similar constructs. SQL server requires that
> when performing inserts, deletes, updates and creates on tables with
> indexed views QUOTED-IDENTIFYERS must be set to ON. Otherwise those
> opererations will fail...
Yes - this is (relatively) common knowledge. It helps to provide complete
information when posting in the newsgroups. Had you mentioned that an
indexed view was involved, someone might have identified the actual problem
a bit quicker. As a side note, you should also verify that any triggers
that may be participating in these actions are not contributing to the
problem.sql