connect to multiple databases

[alexpanjhc]

Hi

I am going to connect to the 5 different DBs (4 are SQL servers, 1 is Access) using QV11.

Right now I am only testing if it is working so what I did is to have 2 connection strings for 2 Databases in the main tab

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=AAA;Initial Catalog=ASI;Data Source=ABCD;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXX;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is eGJGBSECSZMeM);

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=BBB;Initial Catalog=ASMW;Data Source=EFGH;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXX;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is JTLFKSECSZXGH);

and then a new tab

test:

load *;

sql

select A, B, C

from table X ----> this belongs to Database ABCD

union select D, E, F

from table Y ----> this belongs to Database EFGH;

store test into test.qvd;

however, it would not create the table. The connection is fine for individual table.

Is there a way that I can use one(or more) connection string(s) and just use union to get my table without using concatenate?

Thank you!

[#2]

Alex:

Since SQL part is executed with in SQL console its not possible to concat tables with results produced in two consoles.

but however you can use;

Test:

Load *;SQL select A,B,C from table X;

Load *;SQL select D,E,F from table Y;

Kiran.

[alexpanjhc]

Kiran

Thanks for the response.

I am exploring some of the projects an external consultant did for us.

It looks like he used one conncetion string and then he used the union to concat the tables.

this is connection sting he used and apparently he used this stringto connect at least 3 DBs in his project.

ODBC CONNECT TO DBDEV (XUserId is bIZFMaFNfadKGRJOXLJA, XPassword is CfUWMYBIEBJOHYIOELdA);

I am unable to run the qvw because the DB credential had been changed.

But I am guessing it is possible...

[#4]

Alex:

There is a difference between database and server instance. Let me take sql server as example, you can connect to one server which has mutliple databases. Then the union statement will work as the statement runs on a single console.

Multiple connection strings implies multiple servers (not necessarily but generally done as a practise), hence running on a different db console.

Regards,

Kiran.

[alexpanjhc]

Kiran

So what you are saying is that if I am to connect to 2 different servers I will have to have 2 strings?( which makes sense to me.)

and I just asked someone who worked with him before. Looks like he migrated 2 servers into 1.

never mind then... thx!