Insert/update on Informix db through linked server

[Iamblue]

Hi everybody!

I have the following problem:

I need to perform inserts and updates on an Informix database through a linked server.

The linked server uses esker's Tun 2005 odbc connector to connect to informix (Tunodbc200.ifx). I am able to perform selects to Informix database, but when trying to insert/update/delete any record, I get isolation level error.So, I set isolation level on sql server to "read uncommitted", and then, I get error from the odbc controler which says: "controler not capable".

Anybody has faced the same issue??(I've got the necessary acces rights on informix to perform transactions, so it is not a permission issue)

Thank you in advance for your help

 

[Iamblue]

Ops!, by the way, this is the sql sintax I am using to perform querys:

1-SELECT

select * from openquery(Linked_server_name,'select * from Informix_Table').

This works fine

2-UPDATE

update openquery(Linked_server_name,'select * from Informix_Table') set Informix_Table.col2 = 'value2'
where Informix_Table.col1 = 'value1'.

This after setting isolation level to read uncommited, returns "controler not capable" error

3-INSERT

insert openquery(Linked_server_name,'select * from Informix_Table') values ('value1','value2')

same error as update.

I've also tryed to perform transactions using a webservice, but this did´t work too. I got an error that said that the odbc version of the application and the version of the controler did´t match.I tryed with tun's odbc version 1.0 and 2.0 , neither of them worked. Always, got the previous error.

I would really appreciate if someone helps me with this issue.

Best regards,

Asier

 

[PHV]

2- I'd try this:
update openquery(Linked_server_name,'select col2 from Informix_Table where col1="value1"') set col2 = 'value2'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Iamblue]

Hi!
Thanks for you answer!

Well, I tryed what you said, but it did´t work.This is the error I always get:

Servidor: mensaje 7399, nivel 16, estado 1, línea 3
El proveedor OLE DB 'MSDASQL' informa de un error.
[OLE/DB provider returned message: Error no especificado]
[OLE/DB provider returned message: El controlador ODBC no pudo iniciar la nueva transacción.]
[OLE/DB provider returned message: [Esker][xxx.xxx.xxx.xxx:TUNODBC200.ifx][geminix] Controlador incapaz]
Traza de error de OLE DB [OLE/DB Provider 'MSDASQL' ITransactionLocal::StartTransaction returned 0x80004005: ].

where geminix is the name of the linked server and "Controlador incapaz" means "controlller not capable".

People from Esker (the providers of the ODBC connector for informix) have told us that it is possible to perform transactions using that driver.

So...
Should I perform any extra settings on Informix server side?

I`ve tryed the querys form windows server 2003, and from windows 2000, always getting the same error, so I guess that this issue has not nothing to do with windows service packs.

Thanks in advance for your help

 

[jonno232]

Hi,
Could you post how you setup the ODBC connection to work with the linked server?


Many Thanks

 

[Iamblue]

Hi!

This is the configuration:

System DSN:

-Name: Geminix
-Controler: Tun32 driver

The controler is configured as follows:

-Data Source name: Geminix (this is the name of the informix database)
-Description:Geminix
-Server:the IP address of the server
-Service:TUNODBC200.ifx
-Data Base:the data base
-User: the user
-Password : the password

-Controler ODBC version is set to 2.0. I've tryed with 1.0 and 2.0, but always same error.

-No row limit has been set

The linked server uses "Geminix" data source.

This same configuration, but inestead of using informix service, using acces service works.

Linked server configuration:

-General: Microsoft OLDB ODBC provider with datasource="geminix"

-securityut remote user name and password the same as those I used to configure the oDBC before.

-server options : data acces is enabled


Hope this helps you to give me an answer.
Thank you for your help in advance.