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

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

select * from <dbname><ownername>.<tablename>
select * from db1..tab1, db2..tab1 where ...........................

If the databases are on separate servers then 6.5 and below offer remote stored procedures (look them up in the docs) but that is your only server-side choice, there is no way to directly access the tables involved. If you need direct access then you will need to make two separate client connections to each server and process the data at the client end. Something like the JET engine 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 client connection because the fully qualified object name now allows the servername to be passed, so you can do

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


Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish