SET QUOTED IDENTIFIER should be ON when inserting a record

Question

I am stuck in a rather strange problem with SQL Server 2005, which throws

"SET QUOTED IDENTIFIER should be on when inserting record"

(using as SP) to the particular table. This worked fine earlier but is throwing this error randomly.

I have verified the SP. We didn't manually specify SET QUOTED IDENTIFIER settings inside, so it must be ON by default.

Can someone clarify what could be the problem?

The table must be created with SET QUOTED IDENTIFIER ON right? I didn't check the table script yet.

I have observed that this problem only occur with the SPs doing insert or update on a date column (modifiedAt)... A sample value is '2009-08-10 06:43:59:447'..

Is there a problem with the values passed?

Answer 1

After a long struggle we were able to fix this problem. I just wanted to share the reason.

Our build team maintains a separate in-house tool to deploy scripts, which internally triggers the SQLCMD (shell) utility to execute T-SQL scripts in a db.

Here is the culprit: by default, QUOTED_IDENTIFIER is OFF when running in SQLCMD mode!

Every script run through this tool is created with QUOTED IDENTIFIER OFF. We are the only module which uses indexed views. All the remaining stories you know well in my previous posts :(

NOTE: I am going to vote everyone's post as useful.

Answer 2

Script the stored proc, ensure/change SET options, run the ALTER PROC to ensure SET QUOTED IDENTIFIER ON is set.

Why?

The setting of "SET QUOTED IDENTIFIER" is defined at creation time for stored procs and is always "ON" for tables. Source, BOL.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

The default for connections can be defined at the server level (sp_configure 'user options') or database level (ALTER DATABASE). For SSMS, it's under "Tools..Options.. Query Execution..SQL Server..ANSI". It's also the default for client libraries too (except DB-LIb).

Now, it you open an SSMS Query Window and start typing "CREATE PROC.." then it uses SSMS settings when you run the code.

And SET QUOTED IDENTIFIER can not be set at run time inside the stored proc. Show me the a reference before you disagree... From the MS BOL link above:

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

You have to work hard to run any code with this OFF... so the most likely fix is to ALTER or re-create the stored proc.