IBM Support

How to set up and test a simple OLE DB Linked Server in Microsoft® SQL Server to allow retrieval of data in an IBM Informix database server

Troubleshooting


Problem

How to set up and test a simple OLEDB Linked Server in Microsoft® SQL Server to allow retrieval of data in an IBM Informix database server

Resolving The Problem

In order to set up a linked server from SQL Server the following checks need to be made.

  • Make sure you have the correct Informix Client SDK (32 or 64 bit) for the SQL Server (32 or 64 bit) you are using
  • Make sure the Informix OLE DB provider has been registered .

      • From a command prompt run the appropriate regsvr32 application to register the Ifxoldbc provider.
        regsvr32 ifxoledbc
  • Make sure that the coledbp.sql script has been run against the sysmaster database for the Informix Instance. The coledbp.sql script can be found in the $INFORMIXDIR\etc directory of the Windows client machine where IBM Informix Client SDK or IBM Informix Connect is installed.

    • Note - This should be run on the Server with the Informix Instance ( not the Microsoft® SQL Server )
        dbaccess sysmaster coledbp.sql
    If the above script is not run against the IBM Infomrix Instance then the following error can be seen when attempting to select via a linked server.

      OLE DB provider "ifxoledbc" for linked server "demo_on" returned message "EIX000: (-111) ISAM error:  no record found.".
      Msg 7311, Level 16, State 2, Line 1
      Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "ifxoledbc" for linked server "demo_on". The provider supports the interface, but returns a failure code when it is used.

    The Linked Server can now be created.
      From the Microsoft SQL Server Management Studio
        In object explorer find your SQL Server instance ( up and running )
        Click Server Objects -> Linked Servers , right click and "New Linked Server "



      In this dialog box, set the following fields:
        • Linked server: Specify the name for the SQL Server to link.
        • Provider: Choose IBM Informix OLE DB Provider from the drop-down list.
        • Product name: Specify the name of the Informix provider, which in this example is ifxoledbc.
        • Data source: Specify the name of the data source as database@server.
        • Provider string: Specify any additional connection string parameters that the provider uses.
The user ID for the Informix database server might differ from the one that is used with SQL Server. If so, you need to set a remote user mapping under the Security Page.




The Linked Server can be used in the following manner from an SQL query
    • select * from demo_on.stores_demo.informix.systables where tabid<3;
    • select * from Openquery(demo_on,'select * from customer');

More information about the Informix OLE DB Provider and other Informix application development can be found in the IBM Informix Developers Handbook

The IBM Infocenter also has further information - Introduction to IBM Informix OLE DB Provider

Your Passport Advantage site will have the Client SDK ( containing the driver ) for download

Related Information

Linked Servers

[{"Product":{"code":"SSVT2J","label":"Informix Tools"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Informix Client Software Development Kit (CSDK)","Platform":[{"code":"PF033","label":"Windows"}],"Version":"1.0;1.1;1.2;1.3;1.4;1.5;1.6;2.0;2.1;2.2;2.3;2.4;2.5;2.6;2.7;2.8;2.9;3.0;4.0;4.1;4.2;5.1;6.0;7.2;7.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
20 January 2022

UID

swg21195578

Page Feedback