quoted_identifier

Hi
I am getting this strange error :
Server: Msg 1934, Level 16, State 1, Procedure
dataIntegration_mergeCompanyData_prc, Line 90
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'
the problem seem to be with a table in an update statement in the proc where
it references a table in an index view. if I change the left outer join of
the update statement to an inner join, the error goes away, if I uncomment
the schema_binding in the index view, the error also goes away.
anyone know what is going on?
thanks
PAny time you access an indexed view the connection must have certain SET
settings such as QUOTED_IDENTIFIER set a certain way. Look up Indexed Views
and then Set options that affect results underneath that for details.
Andrew J. Kelly SQL MVP
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:79578F94-688A-4A17-B8BA-D6418C437AEA@.microsoft.com...
> Hi
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where
> it references a table in an index view. if I change the left outer join
> of
> the update statement to an inner join, the error goes away, if I uncomment
> the schema_binding in the index view, the error also goes away.
> anyone know what is going on?
> thanks
> P
>|||alfred (alfred@.discussions.microsoft.com) writes:
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where it references a table in an index view. if I change the left
> outer join of the update statement to an inner join, the error goes
> away, if I uncomment the schema_binding in the index view, the error
> also goes away.
> anyone know what is going on?
As Andrew said, you are performing an update that affects an indexed
view. Whenever the you work with an indexed view, these settings must
be on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDNING,
CONCAT_NULL_YIELDS_NULL and ARITHABORT. All but the last option are
on by default when you connect with any API but DB-Library.
However, for the first two settings, what applies when you run a stored
procedure is not the setting for the connection, as these settings are
saved with the stored procedure.
And very unfortunate, there are two tools for which QUOTED_IDENTIFIER
is off by default: OSQL and Enterprise Manager (the latter also has
ANSI_NULLS off by default). Therefore, if you use these tools, you
must take precautions to make sure that this setting is on. If you
use OSQL, use the -I option to turn on QUOTED_IDENFIER. If you use
Enterprise Manager to edit your procedures, simply stop doing that
and use Query Analyzer instead. QA does not have this issue, and is
better editor anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql