There are 2 approaches to querying other servers and other databases from SQL Server:
* Use SQL Server's linked server functionality
* Use the OPENROWSET sql command
SQL Server LINKED SERVERS
In SQL Server Management Studio, you can create a "linked server" to another SQL Server, or to Access, or to another database like Oracle. Once created, you can then issue sql commands using the linked server. There is special syntax to issue a sql statement against the linked server, but it is not too hard.
OPENROWSET method
The OPENROWSET requires you to configure Ad Hoc Distributed Queries on the SQL Server before it will work. Use sp_configure to turn it on.
SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin';'',Customers) GO
Or if you want to join to another database:
USE Northwind GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders) AS o ON c.CustomerID = o.CustomerID GO