January 9, 2017 at 8:06 am
Hi
I have a small script that runs as an SQL task within SSIS
It's worked without issue for months
USE Phones
GO
sp_MSforeachtable @command1="delete from ? where '?' like '%_st]'"
This morning it generated the following issue:
"DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
I've been able to fix the problem by generating the following code:
USE Phones
GO
sp_MSforeachtable @command1="set quoted_identifier on;delete from ? where '?' like '%_st]'"
It seems to suggest that the Quoted Identifiers Enabled property has been changed (it's currently False)
Without restoring, is there a way to check this?
Has anybody seen this before and if so, am I correct with my 'property change' assumption?
Thanks
- Damian
January 12, 2017 at 8:40 am
Fixed, looks like the creation of a filtered index caused this problem.
Removed the filtered index and it worked
- Damian
January 12, 2017 at 9:00 am
DamianC - Thursday, January 12, 2017 8:40 AMFixed, looks like the creation of a filtered index caused this problem.
Removed the filtered index and it worked
My guess is that the index is not the problem, but HOW the index was created is the problem.
From sql server help:
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.
January 12, 2017 at 9:12 am
Thanks Bill
Yes, the server quoted identifier property is set to OFF so looks like creating the index inherited this
Out of interest (as I couldn't find a decent explanation) do you know why this setting impacts filtered indexes and not non-filtered ones?
- Damian
January 12, 2017 at 11:49 am
DamianC - Thursday, January 12, 2017 9:12 AMThanks BillYes, the server quoted identifier property is set to OFF so looks like creating the index inherited this
Out of interest (as I couldn't find a decent explanation) do you know why this setting impacts filtered indexes and not non-filtered ones?
The QUOTED_IDENTIFIER option is at the database level but can be overridden in scripts and connections that create objects. I believe the default for a database and ODBC connections is to set it to ON.
Regular indexes do not do any expression parsing so QUOTED_IDENTIFIER settings are irrelevant. Filtered indexes have a WHERE clause expression and must determine how to interpret the brackets or quotation marks.
I force the recommended set of ANSI settings all through a database for the sake of standardization. That allows me to strip out all ANSI settings from all scripts for simplicity and elegance. Only ANSI override settings can be found in my team's scripts.
January 13, 2017 at 4:32 am
Bill Talada - Thursday, January 12, 2017 11:49 AMDamianC - Thursday, January 12, 2017 9:12 AMThanks BillYes, the server quoted identifier property is set to OFF so looks like creating the index inherited this
Out of interest (as I couldn't find a decent explanation) do you know why this setting impacts filtered indexes and not non-filtered ones?The QUOTED_IDENTIFIER option is at the database level but can be overridden in scripts and connections that create objects. I believe the default for a database and ODBC connections is to set it to ON.
Regular indexes do not do any expression parsing so QUOTED_IDENTIFIER settings are irrelevant. Filtered indexes have a WHERE clause expression and must determine how to interpret the brackets or quotation marks.
I force the recommended set of ANSI settings all through a database for the sake of standardization. That allows me to strip out all ANSI settings from all scripts for simplicity and elegance. Only ANSI override settings can be found in my team's scripts.
Thanks Bill, makes sense
- Damian
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply