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.

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.

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.