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 "
- 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.
- In this dialog box, set the following fields:
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');
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
23 July 2019