Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.
My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.
23 October 2012
Setup with Oracle in SQL Server:
Suppose you want to access data from
oracle or other database products using T-SQL via SQL server database Engine
then you need to configure Linked Server.
You can learn about Linked Server from
Here I show an example to setup linked
server with oracle.
Oracle Configuration on Client machine
First install oracle client on machine
where you want to configure linked server.
Go to [Oracle
Client Installation Directory]\product\11.2.0\]dbhome_1\NETWORK\ADMIN
Open tnsnames.ora file.(It is Network
configuration file for oracle)
Add TNS setting in this file as
Enter USERNAME: username@PROD
Type any select query to confirm
In object explorer, Expand Server
Object tree Node and see linked server
Right click on linked server and
select New Linked Server and Enter
Select Security option and enter
If all things are right linked server
Now Test your linked server.
OPENQUERY(linkedservername,'SELECT *from oracledatabasename.table_or_view') a;
Syntax of OPENQUERY
( linked_server ,'query' )
OpenQuery you can INSERT,DELETE , UPDATE,DELETE.
OPENQUERY(PRODNEW,'SELECT *from Apps.customer_details') a;
OPENQUERY (PRODNEW, ‘SELECT ID,name FROM Apps.customer_details’)
OPENQUERY (PRODNEW, 'SELECT name FROM Apps.customer_details
WHERE id = ''C121''')
name = 'Uma Shankar
OPENQUERY (PRODNEW, 'SELECT ID FROM Apps.customer_details
WHERE ID = ''C121''');
fetch data from oracle via SQL server database engine.
It is the
little about linked server.I have learned this when my client require to
develop a web site that fetch data from sql server but customer data of client
comes from oracle.
- Insert data from Oracle to SQL Server over linked server
INSERT INTO sqldatabasename.dbo.tablename
from oracletablename') a;
- Insert data from SQL server to Oracle over linked server
INSERT INTO OPENQUERY(linkedservername,'select
- Delete data from Oracle over linked server
DELETE OPENQUERY (linkedserver, 'Select *from oracetablename');
In the same way you can create linked server with oracle,mysql and other rdbms and can use above sample query as required.
Update oracle table from SQL Server
SET u.oraclefieldname = ‘value’
openquery(likedservername,'select *from oracletable') as u
where u.hdr_id = @rid
Leave a comment on the original post
[queryingsql.blogspot.com, opens in a new window]