Update Informix Table through OpenQuery

• Manes

  SSC Veteran

  Points: 244

  More actions

  May 27, 2009 at 4:27 am

  #134591

  Dear all... Got task and I desperately need to come up with a solution.

  1)I have two linked servers (Sql2005 and Informix)

  2) Have to perform a query on table on Informix from Sql2005 and after treating the information on sql need to update few fields on Informix

  The select command works fine:

  SELECT * FROM OPENQUERY(INFORMIX, 'SELECT * FROM TABLE_A WHERE TABLE_A.FIELD_B = ''1090835'' ')

  but when i try to update de informix table via this command:

  UPDATE OPENQUERY (INFORMIX, 'SELECT TABLE_A.FIELD_X FROM recibo WHERE TABLE_A.FIELD_B = ''1090835'' ')

  SET TABLE_A.FIELD_X = 'P';

  I get this error:

  Msg 7390, Level 16, State 2, Line 1

  The requested operation could not be performed because OLE DB provider "Ifxoledbc" for

  linked server "INFORMIX" does not support the required transaction interface.

  I have gone through lots of forums but couldnt figure out any solution. I would apreciate any help from the Gurus...

  Thanks in antecipation

  Manes

• Manes

  SSC Veteran

  Points: 244

  More actions

  June 4, 2009 at 8:08 am

  #1004657

  Hi all...

  Got the the problem solved by enabling "Non Transacted

  Updates" in the drivers of IBM Informix OLEDB in the SQL Management Studio, . By default this option is

  not enabled and that's what you have to do.

  (SQL Management Studio, Server Objects -> Linked Servers->Providers and right click on IFXOLEDBC. Provider Option: enable "Non transacted updates".)

  SIMPLE!!! 🙂