I need a query that retrieves info from an Oracle table and a query that retri

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

1 Answers
5854 Views
I also Faced
E-Mail Answers

I need a query that retrieves info from an Oracle table and a query that retrieves info from a SQL..

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

Post New Answer

More SQL Server Interview Questions

Difference between DELETE and TRUNCATE?

0 Answers TCS,

How use inner join in sql server?

0 Answers

* CREATE TABLE [dbo].[t_Colors]([ColorId] [int] NOT NULL,[ColorName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ColorDesc] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ColorIndex] [int] NULL) ON [PRIMARY] GO * insert into [t_Colors] values(101,'Red','',1) insert into [t_Colors] values(101,'Red1','',2) insert into [t_Colors] values(102,'Blue','',1) insert into [t_Colors] values(102,'Blue1','',2) insert into [t_Colors] values(102,'Blue2','',3) * In this table i need to delete DELETE FROM t_Colors WHERE ColorIndex=1 AND ColorId=102 After delete above condition i need to update the ColorIndex set to 1 for Blue1[ColorName] and 2 for Blue2[ColorName] select * from [t_Colors] Note:- how can i get updates the ColorIndex values after delete. for example we need to update Blue1 ColorIndex set to 1 and Blue2 ColorIndex set to 2

0 Answers

Explain subquery and state its properties?

0 Answers

how to find number of columns in a table in sql server 2000 and 2005 also

7 Answers HCL, Virtusa,

Explain indexing and what are the advantages of it?

0 Answers

How to work on DTS?what is the main requirement?

1 Answers Getit, ivan,

what is memory-optimized nonclustered indexes

0 Answers

Do you know what is difference between stored procedure and user defined function?