How do I retrieve data from two tables in two separate databases

I'd like to get a result set that consists of data drawn from tables in two separate databases which are on the same server.  I'm using the following connection string

	"Provider=SQLOLEDB.1;Password=  mypassowrd +
	";Persist Security Info=True;User ID=myusername +
	";Initial Catalog="mydatabase";Data Source=myserver

I'm guessing that there must be a way to list multiple databases in the connection string.  But, I haven't found out how to do it.  And, I've tried using this string to connect to the the 2nd database with the syntax   database2."table name" ... which doesn't seem to work.

Any ideas?

Thanks
0
Robert
10/29/2014 8:23:33 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

7 Replies
684 Views

Similar Articles

[PageSpeed] 3

Any chance you can use FireDAC? I believe in XE7 and later you can do 
this. Otherwise, this issue is specific to database vendor you are 
connecting to.

Robert Byers wrote:
> I'd like to get a result set that consists of data drawn from tables in two separate databases which are on the same server.  I'm using the following connection string
>
> 	"Provider=SQLOLEDB.1;Password=  mypassowrd +
> 	";Persist Security Info=True;User ID=myusername +
> 	";Initial Catalog="mydatabase";Data Source=myserver
>
> I'm guessing that there must be a way to list multiple databases in the connection string.  But, I haven't found out how to do it.  And, I've tried using this string to connect to the the 2nd database with the syntax   database2."table name" ... which doesn't seem to work.
>
> Any ideas?
>
> Thanks
>
0
quinn
10/29/2014 11:58:23 PM
> {quote:title=Robert Byers wrote:}{quote}
> I'd like to get a result set that consists of data drawn from tables in two separate databases which are on the same server.  I'm using the following connection string
> 
> 	"Provider=SQLOLEDB.1;Password=  mypassowrd +
> 	";Persist Security Info=True;User ID=myusername +
> 	";Initial Catalog="mydatabase";Data Source=myserver
> 
> I'm guessing that there must be a way to list multiple databases in the connection string.  But, I haven't found out how to do it.  And, I've tried using this string to connect to the the 2nd database with the syntax   database2."table name" ... which doesn't seem to work.
> 
> Any ideas?
> 
> Thanks

2 DB connections .... 

--
Linden
"Mango" was Cool but "Wasabi" was Hotter but remember it's all in the "source"
0
Linden
10/30/2014 1:32:24 AM
I have established 2 ADO connections, one for each database .  The problem is: I want to join tables from the two databases at the same time.  The database are on the same server.  I can't use both connections in a single query, or at least I don't know how to do so.  That's the problem.   I don't see anyway to access data from a table in each database with any of the ADO tools.


> {quote:title=Linden ROTH wrote:}{quote}
> > {quote:title=Robert Byers wrote:}{quote}
> > I'd like to get a result set that consists of data drawn from tables in two separate databases which are on the same server.  I'm using the following connection string
> > 
> > 	"Provider=SQLOLEDB.1;Password=  mypassowrd +
> > 	";Persist Security Info=True;User ID=myusername +
> > 	";Initial Catalog="mydatabase";Data Source=myserver
> > 
> > I'm guessing that there must be a way to list multiple databases in the connection string.  But, I haven't found out how to do it.  And, I've tried using this string to connect to the the 2nd database with the syntax   database2."table name" ... which doesn't seem to work.
> > 
> > Any ideas?
> > 
> > Thanks
> 
> 2 DB connections .... 
> 
> --
> Linden
> "Mango" was Cool but "Wasabi" was Hotter but remember it's all in the "source"
0
Robert
10/30/2014 10:47:00 PM
> I'd like to get a result set that consists of data drawn from 
> tables in two separate databases which are on the same server.

You just prefix the table name with the database name in the SQL, this
selection is from databases magtelecom and gazetteer.

USE magtelecom
GO
SELECT 
'cabinet'  = w.cabinet, 
'locality'  = w.locality ,
'locality_name' = l.locality_name ,
'post_town' = l.post_town , 
'post_county' = l.post_county 
FROM gazetteer..cabinets w 
LEFT JOIN localities l ON l.locality = w.locality
ORDER BY l.locality_name, w.cabinet
GO
  

Angus
0
Angus
10/31/2014 7:57:35 AM
I appreciate the answer.  But I do understand how to do the query to join two tables in separate databases (and even servers).  What I do not understand is how to do it using the ADO tools in RAD Studio.  Each ADO component requires a connection.  I do not understand how to either list two databases in one connection or two list two connections in a single component.  I am beginning to think that ADO does not provide the tools I need.  I so, what component set might work for me.  I have XE6, but for this 
task I need to use 2007.  And to make things worse, the databases I have to work with are on  SQL Server 2K.    

I should have spelled this all out in my original post.  But, since I'm in the ADO forum, I assumed all answers would be in the ADO framework.

I have established two connections, but haven't found a way to combine them directly.  I can do this task by running two separate ADODataSets and then combining the results using a bunch of code.  I would be much cleaner to issue a single SQL Select like the one shown below to get a single dataset.



> {quote:title=Angus Robertson wrote:}{quote}
> > I'd like to get a result set that consists of data drawn from 
> > tables in two separate databases which are on the same server.
> 
> You just prefix the table name with the database name in the SQL, this
> selection is from databases magtelecom and gazetteer.
> 
> USE magtelecom
> GO
> SELECT 
> 'cabinet'  = w.cabinet, 
> 'locality'  = w.locality ,
> 'locality_name' = l.locality_name ,
> 'post_town' = l.post_town , 
> 'post_county' = l.post_county 
> FROM gazetteer..cabinets w 
> LEFT JOIN localities l ON l.locality = w.locality
> ORDER BY l.locality_name, w.cabinet
> GO
>   
> 
> Angus
0
Robert
11/4/2014 12:01:57 AM
> I appreciate the answer.  But I do understand how to do the query 
> to join two tables in separate databases (and even servers).  What 
> I do not understand is how to do it using the ADO tools in RAD 
> Studio. 

I am using ADO in RAD Studio.  That particular code comes from a stored
procedure but could have been a SQL query from Delphi.  

I make all my SQL requests by building a SQL statement and receiving a
resultset, using stored procedures makes testing the SQL code much easier
since it's separate to the application. 

DataSetGet.Recordset := ADOConn.Execute (cmd) ;

In my example, cmd might be:  EXEC cabinets_lst_loc 11523

I'm not aware you can join tables from different servers in a simple query,
you'd probably need to create a temporary table with the result from one
server on the other, and query from that. 

Angus
0
Angus
11/4/2014 8:09:33 AM
OK, I finally get it.  I can create a view or stored procedure on the database at the server.  A view gives me an onserver connection to both databases.  The ADO connection doesn't allow me to talk directly to two databases or to join two databases in a query, but does give me access to both through the view, which is a single connection to the server.  FINALLY, 

Thanks

> {quote:title=Angus Robertson wrote:}{quote}
> > I appreciate the answer.  But I do understand how to do the query 
> > to join two tables in separate databases (and even servers).  What 
> > I do not understand is how to do it using the ADO tools in RAD 
> > Studio. 
> 
> I am using ADO in RAD Studio.  That particular code comes from a stored
> procedure but could have been a SQL query from Delphi.  
> 
> I make all my SQL requests by building a SQL statement and receiving a
> resultset, using stored procedures makes testing the SQL code much easier
> since it's separate to the application. 
> 
> DataSetGet.Recordset := ADOConn.Execute (cmd) ;
> 
> In my example, cmd might be:  EXEC cabinets_lst_loc 11523
> 
> I'm not aware you can join tables from different servers in a simple query,
> you'd probably need to create a temporary table with the result from one
> server on the other, and query from that. 
> 
> Angus
0
Robert
11/7/2014 6:02:51 PM
Reply:

Similar Artilces:

how to insert data into two table when two tables depend on each other?
suppose there are two table called tableA and tableB and the situation is the primary key of tableA is a foreignkey of tableB and the primary key of tableB is a foreignkey of tableA.in this type of situation how we can insert data into 2 tables called tableA,tableB.pls tell me how can i handle this situation very efficiently.explain in detail........plzzzzz. Hello my friend, What you are suggesting is a many-to-may relationship, which is bad.  If you really need to do it this way, you need to remove the constraint so that the fields are there to query but the relationships are n...

How to synchronize two table in two database
Database : Sybase 11.0 I have an application in our LAN network, also I have a website on internet(Database is My SQL), I want to distribute one table(I.E. te table which record warehouse information ) to internet database, How to Synchronize these two tables? Thanks in advance! Best regards! Jimmy Nie Does My SQL supports bulk-copy? Check bcp utility in sybase. If it supports 1.bcp out complete table from sybase using -c option or if you want to bcp out only a days worth data (or daterange), then first select with where clause into a temp table and...

Problem in retrieving data from two table when one column entry in each table are same but no column in these table match with each other
Hello there!,       First of all i have two tables . one acts as a master table and other one acts as a details table. Currently the ptoblem with my procedure is, i have entered two entries for one project in the Master table. and to briefly discribe those two entries in the master table , i have entered two entries in the Details table corresponding to the entries in the master table. Now i need to combine the master table entries and the details table entries for the same project which should give only two entreies. but my procedure gives four entries. Could yo...

Connection Hung when a user JOIN two tables on two different data type column via Borland Database Engine...Unable to terminate connection have to reboot.
==================================================== SQL Anywhere Network Server Version 11.0.1.2376 Advanced edition 16 logical processor(s) on 4 physical processor(s) detected. Running Windows 2003 Build 3790 Service Pack 2 on X86 (X86_64) Server built for X86 processor architecture 25165824K of memory used for caching Physical memory allocated for images: 24883928K Address space allocated: 1237664K Using a maximum page size of 8192 bytes ==================================================== When one of our users JOIN two tables on two different data type column via our applic...

Referential constraint between two tables in two databases
Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result. i think not possibleThank uBaba Please remember to click "Mark as Answer" on this post if it helped you. Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211, http://msdn2.microsoft.com/en-us/library/ms188279....

data retrieving from two or more tables
Hello every one,  I m working in asp.net 2.0 I want to retrieve the data from two or more tables and also update,insert procedure how can I do it please help me waiting for your reply Thank you  This can be done very fast with ASP.NET 2.0 data controls, go through the quickstart herehttp://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/gridview.aspx HTHRegards ASP.NET Hosting (HostingFest) Hello,   the procedure that u have send me is only for the single table dta retrieving. but I need to know that, how to retierve data from two or more tables ...

how can i join two tables from two different databases?
hi, i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes... is there any trick in the SQL statment? please help me thank you I'm afraid your're out of luck there, I would move all tables and data from ASPNETDB.MDF to your database and only use that one. It will be...

Joining tables in two separate databases
Can anybody tell me? Does Sybase ASA support querying from one database to another like MS SQL Server does? I would like to join two tables that reside in two separate ASA databases and am not sure how to do it? Can anybody help? Thanks, Kevin Shank You cannot reference db2..table.column but you can use remote server and proxy tables. -- Chris Keating Sybase Adaptive Server Anywhere Professional Version 8 **************************************************************************** * Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition and try o...

Retrieving data from two access databases
In ASP i could use data from two different Access Databases.  I can´t do that in ASP.net. Here is the code I use in ASP:<% Dim DBPath,rs,cn,i DBPath="/sp0.mdb" Set cn=Server.CreateObject("ADODB.Connection") OpenStr="Driver={Microsoft Access Driver (*.mdb)}; DBQ="& Server.mappath(DBPath) cn.Open Openstr Set rs=Server.CreateObject("ADODB.Recordset")   sql= "SELECT cliente, numcli from Tdatos ORDER BY secc,num ; "     Dim DBPath2,rs2,cn2, sql2 DBPath2="/contrat.mdb" Set cn2=Server.CreateObject...

Two Queries from Two different Databases = ONE Display/Table
What is the best approach to handle this situation?  I have three different databases, which has it's own stored procedure.  I need to call them all at page load and piece together the data.  The common demoninator is the date.  2007 JAN FEB MAR APR row 1 50 60 89 63 row 2 44 21 62 46 2006 JAN FEB MAR APR row 1 60 90 65 41 row2 984 650 452 762 Row 1 and Row 2 come from two different databases and stored procedures.  How can I query the data and present it as it's shown above? Thank you!  &qu...

testing two three two three two two
Test ...

How can I write a trigger to syn two tables within two separated DB?
As subject. DS ...

How to get data from two different tables for two different columns of a gridview based on datakey
Hi All,            I have a Gridview with two different columns- "Faculty" coming from tblFaculty and "Teachers" coming from tblFacultyTeacherLink. I want to get data for "Teachers" by comparing FcaultyId in tblFacultyTeacherLink and then getting Teachers name. How to achieve this without using nested gridviews?  My .aspx Page is as-   <asp:GridView ID="gviewFaculty" DataKeyNames="FacultyId" AutoGenerateColumns="false" runat="server" Width="100%&q...

Data Access Layer : Fail to read data from two table by one Table Adapter
I have an Access Data Source containing a few tables.I want to set up a DAL for data access and I add a dataset to my projectHowever, when I added two table into a table adapter, VS can't read any data from the data source (while the connection is successful as the columns and rows are loaded)  The two tables are called bidding and itemHere is the SQL generated:SELECT      item.itemId, item.shortTitle, item.picture, item.durationHour,                     ...

Web resources about - How do I retrieve data from two tables in two separate databases - embarcadero.delphi.ado

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

ERISA and All Claim databases
... laws, on the books in 18 states, requiring self-insured employers to report data about the prices they pay to “all payer claims databases.” ...

Sanders’ campaign looses access to N.H. Democratic voter database - The Boston Globe Sanders’ campaign ...
... the presidential campaign of US Senator Bernie Sanders has been denied access to the New Hampshire Democratic Party’s valuable voter database ...


Under pressure, DNC restores Bernie Sanders campaign's access to their database - Videos - CBS News
A strange twist in the race for the democratic presidential nomination: the campaign of Vermont Senator Bernie Sanders has filed a complaint ...

Over 680TB of data exposed in MongoDB databases
There are at least 35,000 publicly accessible and insecure MongoDB databases on the Internet, and their number appears to be growing. Combined ...

EFF confirms that the DEA has deleted its phone call database
Earlier this year, it was revealed that the NSA's massive surveillance program had a precursor: the Drug Enforcement Administration's USTO, which ...

MacKeeper Leak Highlights Danger of Misconfigured Databases
The Shodan port-scanning service finds at least 35,000 MongoDB databases accessible without a password.

OracleVoice: Oracle Challenges SAP On In-Memory Database Claims
Companies that run SAP’s applications should know that most of those applications can perform much better on Oracle Database than on any other ...

Resources last updated: 12/21/2015 11:03:50 PM