ANSI / QUOTE_IDENTIFIER error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ANSI / QUOTE_IDENTIFIER error

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 06:10:18

I have done a rollout from DEV to QA and loads of SProcs are failing with:

"INSERT 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."

this relates specifically to some Filtered Indexes that I created

If I re-create the SProc with

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

then it all works OK

Can I find out what the defaults are on my QA server? and set them to something sensible so that any scripts I run are "right" in this regard?

Thanks

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-06-15 : 06:18:34

is this not down to the individual databases i.e. sp_dboption 'Dbname'

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 06:19:09

Slightly more problematic is that all existing SProcs (which have not been recreated since the Filtered Index was created) give the same error. Re-creating them with the above settings is fine ... but not practicable to rebuild everything ...

... perhaps there was a specific setting I should have used when creating the Filtered Index?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 06:22:55

dbcc useroptions

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 06:37:26

Thanks guys.

sp_dboption 'MyDEV_DB'

The following options are set:
-----------------------------------
auto create statistics
auto update statistics

dbcc useroptions

textsize 64512
language us_english
dateformat mdy
datefirst 7
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed

sp_dboption 'MyQA_DB'

torn page detection
auto create statistics
auto update statistics

dbcc useroptions

textsize 64512
language British
dateformat dmy
datefirst 1
lock_timeout -1
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
isolation level read committed

so those are set exactly the same

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 06:46:55

Ah ... maybe this is the relevant bit from BoL. However, I don't understand how it would have been explicitly set on DEV because we use the exact same script as we do when creating indexes on QA etc.

"SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail"

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 07:07:11

Nope ...

SET QUOTED_IDENTIFIER ON

Re-create index

Sproc still fails

SET QUOTED_IDENTIFIER ON

Re-create SProc and all is well

This definitely works on DEV and I certainly did not explicitly create the Sprocs with SET QUOTED_IDENTIFIER ON on DEV site, so I reckon it must be some database-level default setting

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-15 : 07:28:57

Quoted identifier and ANSI Nulls settings are stored at the procedure level at time of creation. You'll have to recreate them with the proper settings. If you notice when you script out a procedure or view those 2 settings are always included.

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 09:19:20

Yup, had noticed that scripts always had them, and have been wondering if not putting them in my scripts would catch me out one day.

I'm at a total loss to explain why this works on DEV. Some of the SProcs have not been changed for 5 years or more ... and we've never had QUOTED_IDENTIFIER setting in our SQL scripts.

Could I, somehow, set QUOTED_IDENTIFIER in the application Connection Object that is used to call the SProc? Or must the Sproc have been created with QUOTED_IDENTIFIER?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-15 : 10:13:50

It's possible. There are defaults for these settings at the server, database, and client connection level. All that had to happen is running the CREATE PROC statement on someone else's computer who had different client defaults.

This query will help find the ones with inconsistent settings:

select object_name(object_id), * from sys.sql_modules where uses_ansi_nulls=0 or uses_quoted_identifier=0

Kristen
Test

22859 Posts

Posted - 2010-06-15 : 11:17:36

That's very helpful, thanks ... I'll go and investigate

All queries should have been run from QA on my machine ... although I've changed machine recently (although I didn't think I could set default for QUOTED_IDENTIFIER in QA)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources