June 2, 2016 at 11:08 am
I've been reading articles and trying to find a solution. To keep it simple I have the most basic update statement possible. I stripped all the extra code and ran literally just the update statement. If I run this statement in SSMS it executes and updates the table just fine. However, if I run it through SQL Server Agent it fails. Below is the update statement along with the error message. Any advice?
Query:
update People
set legalLastName = lastName, legalFirstName = firstName
Error:
Executed as user: ApplicationUser. UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed.
June 2, 2016 at 11:54 am
In SSMS, go to Tools/Options/Query Execution/SQL Server/ANSI
and check whether the SET QUOTED_IDENTIFIER check box is set. If yes, modify your query as follows:
SET QUOTED_IDENTIFIER ON;
UPDATE People
SET legalLastName = lastName
, legalFirstName = firstName;
It should now run everywhere.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 2, 2016 at 11:56 am
The CREATE script for the People table should also show you which setting is required.
By default, QUOTED_IDENTIFER should be ON.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 2, 2016 at 1:03 pm
I checked those settings and already had SET QUOTED_IDENTIFER check boxed. I added this SET command at the top of the script and it executed in SQL Server Agent. Thank you very much! 😀
June 2, 2016 at 1:33 pm
Kyle Sherry (6/2/2016)
I checked those settings and already had SET QUOTED_IDENTIFER check boxed. I added this SET command at the top of the script and it executed in SQL Server Agent. Thank you very much! 😀
Thanks for posting back; glad I could help.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply