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 / guest
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
Is This Answer Correct ? | 5 Yes | 1 No |
What are the properties and different types of sub-queries?
Explain different types of lock modes in sql server 2000?
Whether the updations made to the views reflect the original records in the table
What command must you use to include the not null constraint after a table has already been created?
How to change the ownership of a schema in ms sql server?
what are the new features of sql server 2005?
8 Answers HP, Keane India Ltd,
A successfully created SSIS package in SQL Server 2005 runs fine in MS BIDS and Integration Services. But gives error when run through an SQL-Job. What are the possible reasons?
Can we return Data from 4(more than 1) tables in stored procedure?
How to insert data with null values?
What is msdb database? : SQL Server Architecture
How to see existing views in ms sql server?
What are the main differences between #temp tables and @table variables and which one is preferred?