Board index » delphi » ADO & query across multiple databases

ADO & query across multiple databases

Hi

I am Using ADO and Delphi 5 Pro Sp1 and SQL server v7. Is it possible to do
a select query from 2 tables in 2 different databases with 1 single
sql and how do i do it?

Thanks in advance

Regards

--
Ruaan Barnard
Programmer
CCS Software
Bloemfontein
South Africa
+27 51 4322238
ru...@ccs-software.co.za
www.ccs-software.co.za

 

Re:ADO & query across multiple databases


Something like this

SELECT t1.Field1, t2.Field2
FROM Database1..Table1 as t1
JOIN Database2..Table2 as t2
ON t1.JoinField1 = t2.JoinField2

You have to name the table with:
DatabaseName.OwnerName.TableName
If you use nothing for owner like I did the default of dbo is used. Be
careful if someone different from the dbOwner or the Systemadministrator
created the table then you cannot use this default value.

"Ruaan Barnard" <ru...@nospam.ccs-software.co.za> schrieb im Newsbeitrag
news:3b83aa9d_2@dnews...

Quote
> Hi

> I am Using ADO and Delphi 5 Pro Sp1 and SQL server v7. Is it possible to
do
> a select query from 2 tables in 2 different databases with 1 single
> sql and how do i do it?

> Thanks in advance

> Regards

> --
> Ruaan Barnard
> Programmer
> CCS Software
> Bloemfontein
> South Africa
> +27 51 4322238
> ru...@ccs-software.co.za
> www.ccs-software.co.za

Re:ADO & query across multiple databases


Yes. If the databases are on the same server, just specify the database name
and owner name for the tables e.g.

select ContactName from NorthWind.dbo.Customers
union
select fname from pubs.dbo.employee

If the databases are on different servers, you can create a linked-server
(see Books-On-Line) then just add the server name into the query e.g.

select ContactName from NorthWind.dbo.Customers
union
select fname from SomeOtherServer.pubs.dbo.employee

Cheers,
Andy Mackie.

Quote
Ruaan Barnard <ru...@nospam.ccs-software.co.za> wrote in message

news:3b83aa9d_2@dnews...
Quote
> Hi

> I am Using ADO and Delphi 5 Pro Sp1 and SQL server v7. Is it possible to
do
> a select query from 2 tables in 2 different databases with 1 single
> sql and how do i do it?

Re:ADO & query across multiple databases


Hi

Thank you very much. Exactly what I was looking for.

Regards

Ruaan

Quote
"Andy Mackie" <amac...@citynetworks.co.uk> wrote in message

news:3b84b2f2_1@dnews...
Quote
> Yes. If the databases are on the same server, just specify the database
name
> and owner name for the tables e.g.

> select ContactName from NorthWind.dbo.Customers
> union
> select fname from pubs.dbo.employee

> If the databases are on different servers, you can create a linked-server
> (see Books-On-Line) then just add the server name into the query e.g.

> select ContactName from NorthWind.dbo.Customers
> union
> select fname from SomeOtherServer.pubs.dbo.employee

> Cheers,
> Andy Mackie.

> Ruaan Barnard <ru...@nospam.ccs-software.co.za> wrote in message
> news:3b83aa9d_2@dnews...
> > Hi

> > I am Using ADO and Delphi 5 Pro Sp1 and SQL server v7. Is it possible to
> do
> > a select query from 2 tables in 2 different databases with 1 single
> > sql and how do i do it?

Other Threads