How can I access data across two separate databases in SQL Server?

Neil Pike

March 4, 1999

1 Min Read
ITPro Today logo

A. With SQL 6.5 and below this is easy as long as the databases areon the same server. Then just use the fully qualified names of the tablesconcerned :-

select * from .
select * from db1..tab1, db2..tab1 where ...........................

If the databases are on separate servers then 6.5 and below offer remotestored procedures (look them up in the docs) but that is your only server-sidechoice, there is no way to directly access the tables involved. If you needdirect access then you will need to make two separate client connections toeach server and process the data at the client end. Something like the JETengine can be used to make this easier and make the two servers look like one.

With SQL 7.0 you can access tables across servers with a single clientconnection because the fully qualified object name now allows the servername tobe passed, so you can do

select * from server1.db1..tab1, server2.db1..tab1 where...........................

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like