June 27, 2017 at 2:45 am
Hello all
We have an issue that while running a process we get the following error. We are running SQL Server 2014 SP1 but the issue is intermittent which makes it more confusing. Our QUOTED IDENTIFIER setting on sql server is on.
Any ideas why could it be?
QUOTED IDENTIFIER Error on Delivered Code on X Tables
Further investigation has shown that the error we get in X on INSERT TABLE
June 27, 2017 at 3:42 am
Do you have SET QUOTED_IDENTIFIER OFF anywhere in your code, or is your ODBC driver configured to set it to OFF?
John
June 27, 2017 at 3:44 am
zouzou - Tuesday, June 27, 2017 2:45 AMHello all
We have an issue that while running a process we get the following error. We are running SQL Server 2014 SP1 but the issue is intermittent which makes it more confusing. Our QUOTED IDENTIFIER setting on sql server is on.
Any ideas why could it be?QUOTED IDENTIFIER Error on Delivered Code on X Tables
Further investigation has shown that the error we get in X on INSERT TABLE
- SQL error. Stmt #: 1703 Error Position: 0 Return: 8601 - [Microsoft][ODBCDriver 11 for SQL Server][SQL Server]INSERT failed because the following SEToptions have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET optionsare correct for use with indexed views and/or indexes on computed columnsand/or f
Thank you in advance
What is the output of this query?
😎SELECT
CASE
WHEN (256 & @@OPTIONS) = 256 THEN 'QUOTED_IDENTIFIER IS ON'
ELSE 'QUOTED_IDENTIFIER IS OFF'
END AS QI_SETTING
June 27, 2017 at 3:45 am
Both of them are ON ODBC and db settings
June 27, 2017 at 3:46 am
QUOTED_IDENTIFIER IS ON on the query
June 27, 2017 at 5:20 am
The default server connection setting and the default database setting are irrelevant if you're putting SET QUOTED_IDENTIFIER ON in the script you are running. Are you generating dynamic SQL in your script? Maybe data is making it not comply with the rules below:
From BOL:
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
June 27, 2017 at 6:31 am
Note that a stored procedure retains the SET QUOTED_IDENTIFIER settings from when it is created, here is a quick example
😎SET QUOTED_IDENTIFIER OFF;
GO
IF OBJECT_ID(N'dbo.SP_TEST_QI') IS NOT NULL DROP PROCEDURE dbo.SP_TEST_QI;
GO
CREATE PROCEDURE dbo.SP_TEST_QI
AS
SELECT
CASE
WHEN (256 & @@OPTIONS) = 256 THEN 'QUOTED_IDENTIFIER IS ON'
ELSE 'QUOTED_IDENTIFIER IS OFF'
END AS QI_SETTING
GO
SET QUOTED_IDENTIFIER ON;
GO
EXEC dbo.SP_TEST_QI;
GO
IF OBJECT_ID(N'dbo.SP_TEST_QI') IS NOT NULL DROP PROCEDURE dbo.SP_TEST_QI;
GO
CREATE PROCEDURE dbo.SP_TEST_QI
AS
SELECT
CASE
WHEN (256 & @@OPTIONS) = 256 THEN 'QUOTED_IDENTIFIER IS ON'
ELSE 'QUOTED_IDENTIFIER IS OFF'
END AS QI_SETTING
GO
SET QUOTED_IDENTIFIER OFF;
GO
EXEC dbo.SP_TEST_QI;
GO
Output
#1 QUOTED_IDENTIFIER IS OFF
#2 QUOTED_IDENTIFIER IS ON
June 27, 2017 at 9:08 am
thank you will have a look but still i dont get why it failing intermittent with this error.
June 27, 2017 at 9:33 am
could it be that some TSQL was pasted into a job step? the ansi settings in a job are NOT the same default settings as when you runt he code in SSMS
For anything i copy paste into a job step, i make sure to explicitly set the same settings i was expecting in managment studio
here's how i get my current settings/*
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
*/
SELECT ' SET ANSI_NULLS ' + CASE WHEN SESSIONPROPERTY('ANSI_NULLS') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ANSI_PADDING ' + CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ANSI_WARNINGS ' + CASE WHEN SESSIONPROPERTY('ANSI_WARNINGS') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ARITHABORT ' + CASE WHEN SESSIONPROPERTY('ARITHABORT') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET NUMERIC_ROUNDABORT ' + CASE WHEN SESSIONPROPERTY('NUMERIC_ROUNDABORT') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET QUOTED_IDENTIFIER ' + CASE WHEN SESSIONPROPERTY('QUOTED_IDENTIFIER') = 1 THEN 'ON;' ELSE 'OFF;' END
Lowell
June 27, 2017 at 10:07 am
If you have data containing single or double quotes, it will fail in dynamic sql while clean data will pass.
If you are generating dynamic sql it will depend on if identifiers need quoted or not as to whether it fails.
If you are executing several scripts in SSMS it depends on if previous batches set the connection ANSI settings to On or Off.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply