getting quoted identifier issue in sql

Question

I am facing

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' issue on my production environment.

the same stored procedure is working fine on lower environment. please help me to identify the issue.

I am using SQL server 2008.

I also tried with recompiling and creating the Stored procedure.

What does the metadata say in your two environments? Specifically, the `uses_quoted_identifier` column in `sys.sql_modules`. That column will say whether or not QUOTED_IDENTIFIER was on when the procedure was created. — Ben Thul, Oct 12 '15 at 04:59
In both environments? As confirmed by the select above? Trust, but verify. — Ben Thul, Oct 12 '15 at 05:05
Then my next guess is that your calling client has different session settings. I think you can get around this by setting quoted_identifiers on *inside* the procedure. — Ben Thul, Oct 12 '15 at 05:20
I faced a similar issue with [my question here](http://stackoverflow.com/questions/7481441/understanding-quoted-identifier). Hopefully that will show you how I got around it. Not sure why it works in your lower environments, but not in PROD. It would seem that there is some inconsistency in environments or how the script is applied. — Mr Moose, Oct 12 '15 at 06:16

score 1 · Accepted Answer · answered Oct 12 '15 at 04:54

1

try with

SET QUOTED_IDENTIFIER ON
 SET QUOTED_IDENTIFIER OFF

answered Oct 12 '15 at 04:54

Jayanti Lal

score -1 · Answer 2 · answered Oct 12 '15 at 05:43

Are you using dynamic sql (query in a string and executing the query string using sp_executesql) in your stored procedure ?

If yes then SET QUOTED IDENTTIFIER ON at the beginning of the SP and set it off it at the end

If No the SET QUOTED IDENTTIFIER OFF at the beginning

2 Answers2