Hi,
I've been trying for several hours to link an Informix database to a Windows 2000 machine running SQL Server 2000. I installed the latest IBM Client SDK for Informix as well as SP3 for SQL Server 2000.

I created a system DSN, let's name is myDSN, which I tested and connects properly to the database. Then from SQL Server Enterprise Manager, I created a linked table with the following parameters:

Provider Name: IBM Informix OLE DB Provider
Data source name: myDSN
Provider string: "User ID=username;Password=password"

I tried various provider strings like
"User ID=username;Password=password;Data Source=myDSN"
"User ID=username;Password=password;Data Source=mydb@myserver"
"Provider=Ifxoledbc.2;User ID=username;Password=password;Data Source=mydb@myserver"

None seem to work. Everytime I want to list the tables from the Informix DB, (by clicking on Tables in the Enterprise Manager), I get the following error:

ERROR 7399 : PROVIDER OLEDB MSDASL generate an error:
OLE/DB Provider 'MDASQL' IDBInitialize:: Initialize retuned 0x80004005

What could be wrong? Or could someone describe the procedure to follow to link an Informix database to SQL Server 2000?

Thanks.

I have loaded "IBM Informix Client-SDK 2.81" but it has not installed
IBM Informix OLE DB Provider for me to select in SQL 2000. If I can get this I'd like to try also what you are doing.

Scrub that I was on the wrong SQL Server. When connected I can see the correct provide. Thats the good news, the bad news I can't config it to work either. Please let me know if you get this to work.

Well, I got around the problem by using the "Microsoft OLE DB Provider for ODBC sources" and using the common Informix ODBC connection string:

DSN=aaa;UID=user;PWD=pass;DB=dbname;HOST=host;SERV =host;SRVR=host


And it seems to work. Although it *might* be shaky.

Shaky is an understatement. I can see the tables but can't select any data from within SQL Query Analyzer. We need to get someone to tell us how to use IBM Informix OLE DB Provider.

My SQl

select product from [MANSYS].mansys.informix.stockm
where product = '60049999049'

Took 3 mins to return 127 rows !!!!!!!

Try openquerying your stuff,

SELECT product FROM openquery(linkedservername, 'SELECT product FROM stockm
WHERE product = '60049999049')

Yep thats much faster thanks.

I would still like to get the IBM provider working.

Thanks for your help.

Had the same issue at my shop and never resolved it. In fact, we scrapped the IBM provider altogether and use a third party driver called Sequelink.

It works fine and is a whole lot simpler to configure, just have to live in constant fear that IBM will come along with a patch or upgrade someday that will break it. I have a really old version thats been working for years, but you can check it out at:

http://www.datadirect.com/

I hope its okay to post the link here...I have no relationship with the company what so ever and in fact we jsut bought a bunch of licenses and didnt even purchase support for the product and have not had a reason to talk to them since!

Its always in the back of my mind though that we should try and use IBM's version since it seems logical that it would be better or at least safer, but it is such a chore to configure and then theres the issue we run into when using it with SQL server I just hate to fool with it.