Making Microsoft SQL Linked Server and Informix work

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature – which is included in the base product unlike Informix Enterprise Gateway Manager – makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB.

However, there are some settings required to make this work seamlessly, which are described in this article as they are not that obvious.

Content

The basic steps to create a Linked Server for Informix are documented here:

www.ibm.com/support/docview.wss?uid=swg21195578

Most importantly, you must use the IBM Informix OLE DB driver, as Linked Servers using ODBC are not fully functional.

With SQL Server 2014 and IDS 12.10, the following also need setting (defaults are all unchecked):

Errors result unless “Allow inprocess” is set. The other changes are required for full functionality. Once this is done, you can explore Informix tables in SQL Server Management Studio, and reference them directly using SQL Server 4-part naming, i.e.:

Copy to Clipboard
Syntax Highlighterlinked-server.database.owner.table

For example:

Copy to Clipboard
Syntax HighlighterSELECT * FROM IDS12.stores_demo.informix.customer

This is much more usable than wrapper functions such as the one below, which cannot employ nested loop joins between local and Informix tables in the same statement, etc.:

Copy to Clipboard
Syntax HighlighterSELECT * FROM OPENQUERY(IDS12, 'SELECT * FROM stores_demo:customer')

Conclusion

SQL Server Linked Servers can make it equally easy to access tables in other database products using a correctly configured OLE DB provider. For example, instead of unloading data, copying the file produced and reloading into SQL Server, this can be achieved much more efficiently in terms of coding, maintenance and run time with:

Copy to Clipboard
Syntax HighlighterINSERT INTO local-table remote-query-statement

Disclaimer

The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.