Querying Other Databases From SQL Server

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