Dear friends,
I want to create linked server to Informix server, so I do bellow steps
-installed Informix OLEDB provider (IBM Informix Connect v2.9)
-create a Odbc names (informix-bsi) and tested the connection, ok
-On mssql2005 create a linked server as bellow
exec sp_addlinkedserver @server= 'bronz' ,
@srvproduct= ''
, @provider= 'MSDASQL'
, @datasrc= 'Informix_bsi'
, @location= ''
, @provstr= ''
, @catalog= ''
-write a stored procedure to do some data transformations. there a delete
statement in it as
//////
delete bronz.bsi_db.informix.atm_traps where id<=@MaxID and
convert(char(8),datetimesn,121)=@CURDate
///
>>>> problem : I run above stored procedure every 1 minute (though a job),
>>>> but I get error sometimes(about 50%) as
/////////////////
Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "MSDASQL" for
linked server "bronz" could not delete from table
"bsi_db:informix.atm_traps". There was a recoverable, provider-specific
error, such as an RPC failure. [SQLSTATE 42000] (Error 7345). The step
failed.
////////////
interesting thing is that it occurs sometimes not always!!
I worked with delete statement manytimes can't find and problem and also
runned in Informix directly without any problem. it seems that middle
libraries like oledb and odbc make problem.
>> another strange thing is that I convert above delete statement to
(delete bronz.bsi_db.informix.atm_traps where id<=160000) but I get syntax
error and says that this sql in incorrect (delete
bronz.bsi_db.informix.atm_traps where id<=(160000))!!! changed end condition
(add parentheses) and get syntax error
Thanks for any idea
Tarvirdi