Very Computer

I'm using SQL 2000, no service pack yet.

I get the following message sometimes when executing a string of stored
procs.

Server: Msg 1934, Level 16, State 1, Procedure drl_ChemInv, Line 456
INSERT failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.

I don't think I need to change anything from the default, so why does Query
Analyzer turn these on and off when it displays my stored proc?

What should these be set to?  and, why is Query Analyzer always changing it?

Thanks - -

Jim Tebbel

By default Query Analyzer sets a bunch of SET commands that gives ANSI
compliance. If you have not changed anything, QA says SET QUOTED_IDENTIFIER
ON, when you connect.

However, QUOTED_IDENTIFIER is, together with ANSI_NULL, a bit special.
When you run a stored procedure, it is the setting at the time the
procedure was created that applies.

The reason you get this error message that the INSERT statement requires
QUOTED_IDENTIFIER to be on. This could be because you are inserting into
an indexed or partition view.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

I'm inserting into a table with indexes on it, but not a view. (There
isn't even a view on the table I'm inserting into).

It seems like the problem may come from modifying stored procs in 2
different environments. If I do it from Query Analyzer, it preserves
the quoted identifier and ANSI null settings.  But if someone else
changes it using Enterprise Manager, it always recompiles with Quoted
Identifiers off, even if I change the connection properties.

Does the compiled status of the SP have to match the session thats
trying to execute it? Or is Quoted Identifier a property on a table as
well and that has to match?

But there is possibly a computed colunm and which is part of an index?

Quote:> It seems like the problem may come from modifying stored procs in 2
> different environments. If I do it from Query Analyzer, it preserves
> the quoted identifier and ANSI null settings.  But if someone else
> changes it using Enterprise Manager, it always recompiles with Quoted
> Identifiers off, even if I change the connection properties.

Then have an electric shock sent through the keyboard when they use
Enterprise Mangager to edit stored procedures. It is not a very good
tool for the task anyway.

Seriously, since I never use EM to edit SPs myself, I don't know if
it can be tamed to provide the correct settings. You can however
say ALTER DATABASE to provide the correct defaults for the database.
These defaults may be overridden by many tools and libraries that
explicitly sets the options, but assuming that EM sets neither, they
will work here.

Quote:> Does the compiled status of the SP have to match the session thats
> trying to execute it? Or is Quoted Identifier a property on a table as
> well and that has to match?

As I explained QUOTED_IDENTITFIER is an option that is saved with the
procedure. It has not particular connection to the table as such.
But some arrangements like indexed views and index on computed columns
require QUOTED_IDENTIFIER to be set.

--
Erland Sommarskog, Abaris AB

SQL Server MVP