Connecting to Multiple Informix DB's

[pbibler]

I have 11 different databases to query from, each with a different IP address, name, etc. I have installed SDK, the driver version is 3.80. My problem is this:

I have created 11 data sources, 11 entries in the hosts file also, but each data source points to the same host. If I change the host on one data source, ALL data sources change. So I can't query from 2 databases at the same time, or automate the queries, because I have to manually change the host each time. I'm able to query from all db's successfully, there are no connection problems.

I think there's some basic idea I don't understand. Can anyone give me any ideas?

Thanks,

Paul B

 

[pbibler]

I should also add, my goal is to use SQL Server 2005 to automate several queries, so that's the ideal place for a solution. However, I can't make it work using other query tools as well.

Thanks,

Paul

 

[mberni]

maybe a solution would be to create synonyms in ONE single database pointing to the other databases.

example:

in database 1:

create synonym exttable1 for database2:username.sometable;
create synonym exttable2 for database3:username.sometable;
create synonym exttable3 for database4:username.sometable;

then you could do a connect to database1 and select from exttable1, exttable2, etc. which results in queries to the different other databases.

to achieve this, the server on which database1 is located must be allowed to talk to server2, etc.