ODBC connection from 64-bit SQL Server to Informix data source

Question

I am trying to create a linked table in a 64-bit SQL Server to Informix, and the SQL server does not list Informix ODBC in the DSN list. I have downloaded the 64-bit Informix Client SDK, however, despite the fact that it states 64-bit Informix Client SDK, it only appears in the 32-bit ODBC DSN. I tried to create a linked table in a 32-bit SQL Server and I was then able to see Informix ODBC DDN.

I was just wondering that if there is an Informix Client SDK that would show in the 64-ODBC DSN, not the 32-bit?

Also is there a way of linking a 64-bit SQL Server to Informix please? Thanks.

Solution 1

Create a DB Link in SQL Server to Informix

I used the following Software environment:

• SQL Server 2012
• Informix SDK 4.10 FC2
• Informix 11.5

First Set some Properties for Ifxoledbc Driver:

Then Create Linked Server:

General Settings - Datasource is your DB and your Server Name

Security Settings - Add valid Informix Database Credentials

Server Options - Set RPC and RPC Out to True

I use the db link for calling a stored Procedure in Informix and writing the data to SQL Server.

Solution 2

I was able to set up a linked server without Ifxoledbc. Once my System DSN for the 64-bit ODBC driver was set up (and tested OK), I set up a linked server using the Microsoft OLE DB Provider for ODBC Drivers.

Linked Server: UCCX
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: IBM Informix ODBC Driver (probably could be anything but I named it the same as the ODBC Driver and it worked).
Data Source: UCCX (the name of your System DSN).

On the security tab I mapped a local login to the same remote user/password as the one set up in the System DSN itself.

And another way to consume the System DSN is via OPENROWSET. This works even without setting up a linked server:

select *
from    openrowset('MSDASQL', 'DSN=UCCX', '
            select * from ContactCallDetail
            where   StartDateTime >= ''2017-03-10 00:00:00''
            and     StartDateTime < ''2017-04-10 00:00:00''
            limit 10'
        ) p

Solution 3

Did you use the 64 bit version of ODBCAD32.EXE to create the DSN?

There are two versions of the ODBC administrator.

This one is the 32 bit version:

C:\Windows\SysWOW64\odbcad32.exe

It can only see 32 bit drivers, and only 32 bit processes can see it's DSN's

This one is the 64 bit version:

C:\Windows\System32\odbcad32.exe

It can only see 64 bit drivers, and only 64 bit processes (i.e. SQL Server 64 bit) can see it's DSN's

Even better you could define a DNS-less connection which doesn't use a DSN, it uses the driver directly. Then you can avoid this confusing step altogether.

Comments

[SQL Police]

Chapeau that's what I call a great answer !!

[Paul]

Ha! I love the Then you can avoid this confusing step altogether. - The whole process is confusing, if you ask me!

[Nick.McDermaid]

Yes 7 confusing steps are not much less confusing than 8 confusing steps.

[TallTed]

Well... Yes and no. It does deliver a connection from SQL Server to Informix, but it uses an OLE DB Provider for Informix (Ifxoledbc), not an ODBC connection as was requested. (SQL Server is an OLE DB client application, not an ODBC client application; and when you tell SQL Server to target an ODBC DSN, it invisibly uses the Microsoft OLE DB Provider for ODBC to bridge the protocol gap.)