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
No comments:
Post a Comment