sql query on multiple instances

Hi, I'm using Delphi XE with the adodataset to query sql database but I'm now encountering a new situation where I have to query on two different serveurs.   Separatly I can query them but if I want to make a joined query then I'm not sure how to do this since adodataset have only one adoconnection specified.

For exemple, I would lke to query this
select * from customer left join mytable on customer.id=mytable.idcust

Here the customer table and mytable are on two different serveur.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
You can either connect to each server separately and handle the join logic in your code or you have to add a linked server to one of the target servers that would allow you to query one from the other.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Depends on what you are doing and the business logic of your application.
Either as suggested by Brian Crowe, or, if one of the tables is relatively small in size, you could create special table (Temporary Table) in your main server and copy that small table over from the other server, do your join and clear the temp data (or keep if frequently used but update before use).
Sinisa VukSoftware architectCommented:
For such thing I use one of memory table component (TRxMemData, TkbmMemTable, ..). First add contents of fetched AdoDataset1 (from one server - using connection string) and then from another AdoDataset2 (connected to other server). Finally, I've got joined tables. For this - you shoud have same Field names and same order in select query. (field1, field2, ...).
When using connection string property of Ado dataset - you don't need ado connection component.
Buropro-CitationAuthor Commented:
I have tried using sql server management studio.   This is the provider string I'm using:

Provider=SQLOLEDB.1;User Id=MyUser;Password=MyPass  ;Data Source=SQLDB;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDb;

I'm getting an error that says basically that authentification failed even though this is the same string I'm using to connect successfully to the server with adoconnection.
Buropro-CitationAuthor Commented:
I have succesfully linked the server by using the SQL name and the security page.   It is now working :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.