SQL Server Agent Step Failing

Question

Post reply

SQL Server Agent Step Failing

Kyle Sherry

SSChasing Mays

Points: 606
More actions
June 2, 2016 at 11:08 am

#308156

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kyle Sherry SSChasing Mays Points: 606 More actions · Answer 3

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! 😀

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel