Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: Data Migration Informix To SQL Server Using OpenRowSet

    Hello,

    we are moving a lot of data from an informix-Database to SQL Server 7. For doing this fast, we try to use the T-SQL-Statement OPENROWSET; there is defined a System-DSN named test with UID migration and password joker.

    Unfortunately we receive the following errors when testing the Statement with the Query Analyzer:
    First try with SQLOLEDB:
    SELECT Test.* FROM OPENROWSET('SQLOLEDB','DSN=test;UID=migration;PWD= joker','SELECT * FROM datbnk') AS Test
    Result:
    Server: Message-Number 18456, Registration for user 'migration' missed.

    Second try with MSDASQL:
    SELECT Test.* FROM OPENROWSET('MSDASQL','DSN=test;UID=migration;PWD=j oker','SELECT * FROM datbnk') AS Test
    Result:
    Server: Message-Number, OLE DB-Provider 'MSDASQL' announced an error: [OLE/DB provider returned message: Because of the System Error 126 (INTERSOLV 3.11 32-BIT INFORMIX 9) the indicated driver could not be loaded.]

    The connection with ODBC (Connection-String DSN=test;Username=migration;password=joker, Aufruf in ASP-Seite) is working!

    Thanks in advance for any help and tipps, how one of the 2 OpenRowSet-Commands could work,
    Robert.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Data Migration Informix To SQL Server Using OpenRowSet

    Originally posted by jokersoft
    Hello,

    we are moving a lot of data from an informix-Database to SQL Server 7. For doing this fast, we try to use the T-SQL-Statement OPENROWSET; there is defined a System-DSN named test with UID migration and password joker.

    Unfortunately we receive the following errors when testing the Statement with the Query Analyzer:
    First try with SQLOLEDB:
    SELECT Test.* FROM OPENROWSET('SQLOLEDB','DSN=test;UID=migration;PWD= joker','SELECT * FROM datbnk') AS Test
    Result:
    Server: Message-Number 18456, Registration for user 'migration' missed.

    Second try with MSDASQL:
    SELECT Test.* FROM OPENROWSET('MSDASQL','DSN=test;UID=migration;PWD=j oker','SELECT * FROM datbnk') AS Test
    Result:
    Server: Message-Number, OLE DB-Provider 'MSDASQL' announced an error: [OLE/DB provider returned message: Because of the System Error 126 (INTERSOLV 3.11 32-BIT INFORMIX 9) the indicated driver could not be loaded.]

    The connection with ODBC (Connection-String DSN=test;Username=migration;password=joker, Aufruf in ASP-Seite) is working!

    Thanks in advance for any help and tipps, how one of the 2 OpenRowSet-Commands could work,
    Robert.
    Hi,

    if you can from SQL Server load data from a flat file, then it runs
    unload to "flat plain" select * from table....
    from INFORMIX and insert into SQL Server.

    Gustavo.

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Re: Data Migration Informix To SQL Server Using OpenRowSet

    Hello Gustavo,

    thank you for your answer. I think when you tell me about "flat files" you mean text files which can be imported also through OpenRowSet or the DTS-Manager from SQL-Server, is that right?

    Thanks in advance,
    Robert.

    Originally posted by gurey
    Hi,

    if you can from SQL Server load data from a flat file, then it runs
    unload to "flat plain" select * from table....
    from INFORMIX and insert into SQL Server.

    Gustavo.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Data Migration Informix To SQL Server Using OpenRowSet

    Originally posted by jokersoft
    Hello Gustavo,

    thank you for your answer. I think when you tell me about "flat files" you mean text files which can be imported also through OpenRowSet or the DTS-Manager from SQL-Server, is that right?

    Thanks in advance,
    Robert.
    Hi Robert,

    When unloadings an a flat file from Informix, this has the following
    format:

    field_1 | field_2 | ... | field_n |, where "|" is pipe.

    In this format, the file text can load from, for example, EXCEL, etc...
    I think that if You have problem for connect, this is one alternative solution.
    Connect to Informix, unloading data, then connect to SQL and loading from text file with delimiters "|".

    Gustavo.

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Hello Gustavo,

    thank you for your help, sorry for my late answer. We did not solve the problem until now, but we try to get data from the informix-database with an OLEDB-driver.

    Best regards,
    Robert.

Posting Permissions

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