dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: SQL Server
Expertise: Beginner
Apr 24, 2000

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


Joining Queries from Oracle and SQL Server Databases

Question:
I need a query that retrieves info from an Oracle table and a query that retrieves info from a SQL Server table. The info has to be joined together according to Record ID numbers. I have very limited access to the Oracle database but full control of the SQL Server database.

How do I join two different queries from two different databases?

Answer:
To query to different data sources, you can make the Oracle server a linked server to the SQL Server server. A linked server can be any OLE DB data source and SQL Server currently supports the OLE DB data provider for Oracle. You can add a linked server by calling sp_AddLinkedServer and query information about linked servers with sp_LinkedServers.

An easier way to add a linked server is to use Enterprise Manager. Add the server through the Linked Servers icon in the Security node. Once a server is linked, you can query it using a distributed query (you have to specify the full name).

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

SELECT emp.EmloyeeID, ord.OrderID, ord.Discount
FROM SQLServer1.Northwind.dbo.Employees AS emp,
OracleSvr.Catalog1.SchemaX.Orders AS ord
WHERE ord.EmployeeID = emp.EmployeeID
AND ord.Discount > 0
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date