Informix Linked server 1

[thendrickson]

I am attempting to create a linked server (Informix)

I use the following to create the linked server.

use master
exec sp_addlinkedserver @Server = 'traintcpTest',
@srvProduct = 'IBM Informix 3.82 32 bit',
@Provider='MSDASQL',
@provstr = 'Driver={IBM Informix 3.82 32 bit};Database=jlk_stage;UID=me;PWD=something;
PROTOCOL=onsotcp;HOSTNAME=\\devserver;
SERVICE=1527;SERVER=traintcp;'

Although I have changed the UID and PWD to a generic value for this post, the rest of the values are exactly what the values are for a user dsn that works properly

As soon as I click on the created linked server and click tables, I receive an

error 7399 OLE DB Provider 'MSDASQL' reported an error

Driver's SQLSetConnectAttr failed],
Driver's SQLSetConnectAttr failed],
Invalid SQLHostsfile format.]

etc. etc.

I am using SQL Server 2000.

I am doing a google search of course, but not finding the fix. from what I am finding, I seem to be "doing it right" so I suspect I am just plain missing the obvious.

Any ideas?

 

[thendrickson]

I found a typo in the provider string. Additionally I found some other information.

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;'


I now 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(with the same results by the way), the driver I am using is listed in the dropdown box.

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

 

[thendrickson]

if any one has any interest

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

 

[Madbull]

Hey yall, didn't really read your posts completly but i might be able to help you since i can connect to my informix DB thru odbc drivers in visual basic.
Here is my connection string to the informix server:
Dsn=baan;Driver={INFORMIX 3.82 32 BIT};Host=baan;Server=ol_baan;Service=512;Protocol=onsoctcp;Database=baandb;UID=baan;PWD=baan
I also have a SYSTEM (i insist on it,system,not user) ODBC source on my computer called Baan (DSN)
It was really tricky in order to make it work correctly and the rtick reside in the way you set up the client local and DB local in the ODBC source properties.
For myself,my client local is en_US.8859-1 and DB local is en_US.819 and the checkbox'use server database local' is checked...
Hope this might help you...

 

[thendrickson]

thanks MadBull,

I used a similar string in VB to connect to the Informix Server, but I needed to use a linked server since I need to join SQL tables with Informix tables in a select statement.

I am still working on the problem, but I did create an OLE DB connection that works, but have a problem with accessing views as well as tables containing a float data type.

After much research, stress and aggrivation, I seem to have stumbled across some information that let me ask the proper question to IBM support (Very helpful individuals there by the way).

As soon as the Informix DB runs 2 scripts for me, I will test and see if everything works well then post the results with a full description of what I discovered.

 

[Madbull]

In fact i might be intersted by this.Let me explain you,
we use informix here but it's too slow when accessing through odbc so i set up an sql msde2000 database to test if it would be faster...and in fact,it's about 10 time faster!!!unbelievable.
Now,i'm gonna have to find a way to synchronize the tables of both database in order to have the sql one up to date.
I started coding in vb but the problem is i have two diff connection to handle and its not easy.In fact it's also slow.
So all your story about having one connection on 2 database (if i understood correctly) is interesting me.
Please let me know how it works it would be kind and maybe helpfull

 

[thendrickson]

Keeping in mind that I am not doing well at this point ....
Or not yet.

Here is what I have so far.

I have set up a linked server to the Informix server (Through SQL Server) using the OLE DB provider.

To do so, download the newest Client SDK from IBM's website

The install will contain 2 scripts in the Informix\etc directory (doledbp.sql and coledbp.sql) Run the drop first then the create on sysmaster as user informix.

Then create the linked server using Enterprise Manager

Choose the IBM OleDB provider.
Datasource = dbname@servername
ProviderString = decasr8=R8;DB_LOCALE=en_US.819

Server Options Tab
Select collation compatable in addition to the 2 defaults.

Security tab; Whatever you use.

At this point you should be able to see tables and views on the Informix server.

However, I receive errors when I try to execute a query.

IBM Tech has a similar set up testing and they are working with me on resolving my problem.

Good luck if you try this

 

[thendrickson]

Well

I have it worked out.

Once you create the linked server using the newsest version of IBM's Client SDK, the only problem I have found is with the treatment of the IBM time data type by SQL Server.

Any time I will have a time datatype, I am using the Informix extend function to extend the date datatype column out to a full date time column.(in an Informix View)

That gives me todays date with the origional time.

Query the view using Datepart in SQL to get the origional time values.