0

I am getting the following error on a very basic update query when it is executed as job in SQL Server 2008. It runs as expected when I run it manually in SSMS. Can somebody help me figure out what is going on to cause this? The database settings have Quoted Identifiers Enabled set to False. I have tried adding SET QUOTED_IDENTIFIERS TRUE to the job and that did not work either.

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.

The query is as follows (table and column names have been changed to protect the innocent)

UPDATE Table1
SET Column1 = 'C'
WHERE col_status = 'A' AND emp_number
IN (SELECT emp_number
FROM Table2
WHERE emp_status = 'T')
Steve Salowitz
  • 1,283
  • 1
  • 14
  • 28
  • 3
    Check this post, and check if you have triggers on the updating table https://stackoverflow.com/a/10523803/4608204 – EzLo Oct 30 '19 at 13:50

1 Answers1

1

You need add below two set statements while updating table1.

You can use join directly to update table1.

SET QUOTED_IDENTIFIER ON;

UPDATE T1
SET T1.Column1 = 'C'
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T2.emp_number = T1.emp_number
AND T1.col_status = 'A'
AND T2.emp_status = 'T';

SET QUOTED_IDENTIFIER OFF;
JIKEN
  • 337
  • 2
  • 7