Informix Linked from SQL Server 3

[thendrickson]

I had poste in the SQL Sever admin forum, but no results so I though I would check here

I am trying to create a linked server from SQL 2000 to an IBM Imformix database on a server running HP Unix

Currently i am trying to execute

exec sp_addlinkedserver @Server = 'traintcpTest1',
@srvProduct = 'ifxoledbc',
@Provider='MSDASQL',
@provstr = 'Driver={IBM Informix OLE DB Provider};Database=jlk_stage;UID=me;PWD=something;
PROTOCOL=onsoctcp;HOSTNAME=devserv;
Service=1527;SERVER=traintcp;'

This creates a linked table in SQL Server

In Enterprise manager, I click on the server and attempt to look at the tables

I then receive an error message 7399 etc then
"datasource name not found and no default driver specified"

since the database name is correct and I used an IBM utility program and find the sever name and host to be identical, I am suspecting the driver.

But when I try to configure the linked server from Enterprise manager, the driver I am using is listed in the dropdown box exactly as I copied it. The link fails with the same message.

I have an ODBC connection that works, but when I try to link using that connection, I get the same error

There seems to be something I just do not find as I search the net that I have "goofy"

 

[mberni]

you didn't supply the name of the odbc-datasource.

have a look here:

http://msdn2.microsoft.com/de-de/library/ms190479.aspx

 

[thendrickson]

Thank you. I discovered that late yesrerday. I did state I was doing something "goofy". I just could not see it

I did manage to make the connection finally using an ODBC data source, but performance was horrible.

a simple select * from tablename that returns 25 records took 8 to 10 seconds to execute. Average was 9 seconds.

I found a connection string for an OLE DB connection on the net, but I need the Systems Admin to get me permissions to register a dll on the development server in order to test.

 

[mberni]

ok, concerning your performance issues ... which version of the informix-odbc are you using?

we had similar problems with some other 3rd-party application and everything was fine, after we upgraded the odbc part, without changing the application at all.

 

[thendrickson]

IBM Informix 3.82 32 bit

I have also found a connection string for OLEDB I receive an error 7403 which indicates either the dll is not registered (it is now) or the provider name is wrong


I am using:

exec sp_addlinkedserver @Server = 'test6',
@srvProduct = 'ifxoledbc',
@Provider = 'ifxoledbc.2',
@datasrc = jlk_stage@traintcp,
@provstr = 'Provider=Ifxoledbc.2;Data Source=jlk_stage@traintcp;Persist Security Info=true'

I obtained the String from the net and it is exactly as I copied it except for the datasource

as a note, I also tried ifxoledbc, ifxoledbc.1, infxoledbc.3

I am a bit desperate right now since I have "wasted" a lot of time, but the other solution to our problem here is much worse

 

[thendrickson]

I finally found the problems and managed to set up a link to an Informix database from SQL Server.

I there is a SQL Script which needed to be executed agains the Informix database first.

Do a search on IBM's website for coledbp.sql and download that script.

The provider name should be ifcoledb not ifxoldbc

download the newest Client-SDK from IBM and register the ifxoledbc.dll using regsvr32. Do not trust the install to register the dll properly.

The data source should be [databasename]@servername.

The 4 part namin should use a blnk for the databasename.

ie: SQLServerLinkName..UserName.Table.

I now have a workable link to Informix using OLEDB. When I query from within a stored procedure performance is good

 

[mberni]

Hi, sorry for not further contributing to this, but i have been on holiday for a week, so i could not answer.

Is there anything left you need help on or are you satisfied with your current solution?

 

[thendrickson]

I appreciate your help very much.
As is often the case for me, your information helped me reason out from that point and I was able to create a different error number which lead my search in a different direction that lead me to some more information and so on and so on.

Once I found the script to run against the Informix Data Base, my OLE DB conection worked and my performance increased for the ODBC connection.

Initial comparison tests show no performance differences between the ODBC and OLEDB when I execute queries from within a SQL Stored procedure.

I am stiking wilth the OLEDB connection since I am much mor comfortable that I understand potential problems over the ODBC connection.

And the typo of Blnk should read Blank in my last post

 

[mberni]

ok, what i can contribute is that we don't use any oledb-connections, because everything i have heard about that was not much fun

our development-env supports native informix-connections so we don't have to meddle with odbc either.

when you dive into the link i sent you above you should normally be able to setup a working odbc-link too. i am not so familiar with sql-server so i cannot give you more support on that. maybe have a look into a different forum for that.