A very old client of mine yesterday pinged me with this question about error related to QUOTED_IDENTIFIER.
When I run my query it works just fine in SSMS but when I run it via SQL Server Agent Job, it gives me following error. Can you give me solution?
Here is the error he was facing:
Msg 1934, Level 16, State 1
UPDATE Failed Because the Following SET Options have Incorrect Settings: ‘QUOTED_IDENTIFIER’.
Solution / Workaround
The problem he was facing was very simple to fix it. He was running an update and which was working just fine in SSMS, because the SET options of the SSMS windows were different than the one for the query he was running.
Here is the solution of this simple error:
SET QUOTED_IDENTIFIER ON GO -- Write Your Query
When you write turn on the settings for the quoted identifier it will automatically remove the error for you. I hope this simple fix will help you.
If you want to read more about what is quoted identifier, you can read my following blog where I have explained the same issue in the detail.
- SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage
- SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
Reference: Pinal Dave (http://blog.SQLAuthority.com)