DataStage Job fails with Error message UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Problem:

A DataStage job that accesses SQL Server using ODBC receives the following error when try to UPDATE or DELETE records:

ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 1,934: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]DELETE 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.

ODBC function "SQLExecute" reported: SQLSTATE = 42000: Native Error Code = 8,180: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (CC_OdbcDBStatement::executeDelete, file CC_OdbcDBStatement.cpp, line 1,278)

Cause

When the configuration was made for the ODBC database connection, one of the following options was not set:

Windows: Enabled Quoted Identifiers

Unix: QuotedId

Resolving the problem

Windows:

Edit the ODBC database configuration through the Administrative Tools, ODBC Data Sources, and check Enabled Quoted Identifiers

Here is how to enable for Wie Protocol and Native Wire Protocol drive:

For SQL Wire Protocol drive, Click Advance tab and check Enabled Quoted Identifiers

For SQL Server Native Wire Protocol drive,  click on Advance Tab and check  Enabled Quoted Identifiers.

Linux:

Determine the location of the .odbc.ini by examining the Environment Variable Settings Log entry in the DataStage job log. Look for System Variable ODBCINI

Edit the .odbc.ini and set QuotedId=Yes

Here is an example:

[DsodbcM]
Driver=#BRANDED_ODBC_DIR#/lib/VMmsss24.so
Description=DataDirect 6.0 SQL Server Wire Protocol
Address=193.128.90.32,1433
AnsiNPW=Yes
Database=Dsodbc
LogonID=dsqa
Password=dsqa
QuotedId=Yes

Reference:

IBM Online Document: http://www-01.ibm.com/support/docview.wss?uid=swg21587480