Retrieving data from two separate databases

It's hard to imagine, but there may be a time where you have a need to retrieve information from both a SQL Server database and a non-SQL Server database. When faced with that situation it's good to know your options.

Here are two ways you can achieve your goal:
  1. Set up the non-SQL Server database (let's imagine that it is an Oracle database since SQL Server currently supports the OLE DB data provider for Oracle) as a LINKED server to the SQL Server database. You can add a linked server by using the sp_AddLinkedServer command and query information about the linked server by using the sp_AddLinkedServer command.
  2. The easier option is to use Enterprise Manager to add a linked server. To do this, go to through the Linked Servers icon in the Security node. Once a server is linked, you can query it using a distributed query.

Here's an example of a distributed query that queries the Employees table in SQL Server and the Orders table from Oracle:

SELECT�emp.EmloyeeID,�dept.DeptID,dept.deptno
FROM�SQLServer1.Aruba.dbo.Employees�AS�emp,
OracleSvr.Catalog1.SchemaX.Department�AS�dept
WHERE�dept.EmployeeID�=�emp.EmployeeID
AND�dept.deptno�>�10
Home��Next
Hosted by www.Geocities.ws

1