sql server - 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.