ODBC - Delete failed with SET option error

[Kirtikumar]

Hi

I am trying to delete the data from a SQL server 2005 table. I am using DS server job with ODBC driver for it.

For all the tables the DELETE stataments are working fine except for one table and its throwing following error.

Code: Select all

djsClearRFCore..tblRFCore.lnkClearRFCore: DSD.BCIOpenW call to SQLPrepare failed.
DELETE FROM I_GEN_INST_HIST where I_GEN_INST_ID = '200000000001'
SQLSTATE=, DBMS.CODE=1934
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ANSI_NULLS, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
SQLSTATE=, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Looking at the error I added SET statements for mentioned errors and tried running it. But it again failed with the same error.

I searched the forum, but it seems no one has faced this problem. Any inputs?

[kris]

Is this value '200000000001' supposed to be a string or a numeric?

[Kirtikumar]

Actually first I tried deleting all the data without the where clause and it was failing.
So I added where clause. Though the value looks numeric, in table the column datatype is VC.

[kris]

Is it working now?
Are you using user defined SQL?

Take a look at this post and see if it can be of any help.

http://dsxchange.com/viewtopic.php?t=11 ... e0b3f57959