Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Linking an Informix DB with SQL Server 2000

    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.

  2. #2
    Join Date
    Jul 2003
    Posts
    23
    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.

  3. #3
    Join Date
    Jul 2003
    Posts
    23
    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.

  4. #4
    Join Date
    Feb 2004
    Posts
    3

    Thumbs up Way around

    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.

  5. #5
    Join Date
    Jul 2003
    Posts
    23
    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 !!!!!!!

  6. #6
    Join Date
    Feb 2004
    Posts
    3

    OpenQuery

    Try openquerying your stuff,

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

  7. #7
    Join Date
    Jul 2003
    Posts
    23

    Thumbs up

    Yep thats much faster thanks.

    I would still like to get the IBM provider working.

    Thanks for your help.

  8. #8
    Join Date
    Jan 2004
    Posts
    71

    IBMs OLEDB provider was bad juju for us

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •