1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

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?

Question Posted / guest

Answer Posted / 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



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What are the tool windows in sql server management studio? : sql server management studio

620

What is the significance of null value and why should we avoid permitting null values?

530

What is difference between joins and subqueries?

532

How to find the login name linked to a given user name?

528

1.how to find the dead lock in sql server? 2.How to fine the memory leaks in sql server? 3.suppose transaction log file increasing what action will take ?

1550




Write a program using SQL queries to find a unique entry in a table.

540

What is the command used to recompile the stored procedure at run time?

618

Is there any difference between the primary key and unique key?

567

What are views in ms sql server?

594

List some case manipulation functions in sql?

593

What is molap and its advantage? : sql server analysis services, ssas

545

What is etl - extraction, transformation, and loading?

521

Can a table have 2 foreign keys?

501

Explain Reporting Life Cycle?

90

What are the database roles? : sql server security

557