SQL Server Pro
Connect With Us
Home > SQL Server > Setting Up an Oracle Linked Server

Setting Up an Oracle Linked Server

Apr. 19, 2006 Michael Otey | SQL Server Pro

Related: Connecting SQL Server and Oracle Using Linked Servers

SQL Server Linked Servers feature lets you access Oracle data and data from other OLE DB/ODBc compatible data sources from SQL Server. Here are the basic steps for setting up an Oracle linked server.

1. Install and Configure the Oracle Client Software

Oracle client software provides the network libraries required to establish connectivity to an Oracle database system.Download the software from http://www.oracle.com/technology/software/products/database/oracle10g/index.html. Install the software on your SQL Server system and configure it by using Oracle Net Configuration Assistant.

2. Create the Linked Server

Create a linked server by using the T-SQL command

EXEC sp_addlinkedserver
  'OracleLinkedServer', 'Oracle',
  'MSDAORA', 'OracleServer'

The name of the linked server is Oracle-LinkedServer.The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider. MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.The final required parameter is the data source name, Oracle Server.

3. Add Logins for the Linked Server

Next, provide the SQL Server system with an Oracle login to access the Oracle database by using the sp_addlinkedsrvlogin command

EXEC sp_addlinkedsrvlogin '
  OracleLinkedServer ', false,
  'SQLuser', 'OracleUser',
  'OraclePwd'

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.The second parameter determines the name of the login to be used on the remote system. A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case. A value of False means you'll supply the remote login.

The third parameter specifies the name of a SQL Server login that this remote login will map to. A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login. The fourth and fifth parameters supply login and password values for the Oracle system.

4. Query the Linked Server

To test the connection, run a sample query using the linked server name. Linked servers support updates as well as queries.To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name. For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM
  OracleLinkedServer..SCOTT.EMP


5. List the Linked Servers

To list your linked servers and show the OLE DB provider that they employ, use the sp_linkedserver stored procedure.

Related: Troubleshooting Oracle Client Connections

Discuss this Article 10

AnneG_editor
on Mar 26, 2008
PVR95, thanks for your comment. Here's Michael Otey's response: "The Oracle Client is still required to connect to an Oracle system. It is used by the Oracle OLE DB Provider. The download is free and it includes the Oracle Client software but I do think there are licensing costs associated with using the client." Thanks for reading!
Muhammad (not verified)
on Jun 1, 2006
Can a user (domain user not belonging to local administrator group) accesses the linked server? I am talking about Excel Linked Server which is defined using Admin and Blank password
Anonymous
on May 28, 2010
Can I link an Oracle 7.3.4 database (32 bit) to SQL Server 2005 (64 bit) using the 16-bit Oracle 7.3.4 client? Do I need to do anything special to make that work?

Thanks in advance!
PVR95 (not verified)
on Mar 26, 2008
Hi, Thanks a lot. This information is very much useful for me and that too you replied in very short span of time. Once again, Thanks. Can we use Oracle Light Version i.e. "ORACLE INSTANT CLIENT" instead of Oracle Client Software to connect Oracle using Linked Server in SqlServer 2005. This Light Version seems to be license free version. I would like to try this Light version. Can you please share your views, similar instances with us Thanks & Regards, Palani
Yolanda (not verified)
on Apr 25, 2008
Is it good practice to install version 8 Oracle Client software to establish a linked server to an Oracle 9i Release 2 database?
pereira_mel
on May 27, 2010
Can I link an Oracle 7.3.4 database (32 bit) to SQL Server 2005 (64 bit) using the 16-bit Oracle 7.3.4 client? Do I need to do anything special to make that work?

Thanks in advance!
PVR95 (not verified)
on Mar 25, 2008
Hi Michael, Is it must to have Oracle Client Software to establish the Linked server to oracle database. My worry is Oracle Client software seems to be a licensed version and huge cost. Is there any other means other than using Oracle Client software to establish linked server to oracle. I think Oracle OLE DB provider for Oracle may help. I think it's a license free download. Please give us your views,opinion Thanks & Regards, Palani
smccown
on Sep 2, 2008
Thanks man, I needed to setup a connection to Oracle and this got me up and running in just a couple mins.
ron.klimaszewski
on Jun 1, 2010
This is an old article that was mailed out on 5/27/2010. There is one major error with this article: "MSDAORA" is the Microsoft-supplied Oracle provider. If you are going to install the Oracle client, use the "OraOLEDB.Oracle" provider.

Tip: If installing the Oracle client on an x64 server, install both the 32- and 64-bit clients.
pereira_mel
on May 27, 2010
Can I link an Oracle 7.3.4 database (32 bit) to SQL Server 2005 (64 bit) using the 16-bit Oracle 7.3.4 client? Do I need to do anything special to make that work?

Thanks in advance!

Please or Register to post comments.

Related Articles
IT/Dev Connections

Las Vegas
September 30th - October 4th

Paul ThurottOur Experts will show you:
• Common SQL Server
Problems
• Best Practices for T-SQL
• SQL Server Integration
Services
• Database Development

Come See Mike Otey & Tim Ford in Person!

Early Registration Now Open

From the Blogs
May 9, 2013
blog

My ISO 8601-Compliant Signature 2

My family recently just "officially" announced that we're in the process of adopting a child from South Africa. We're quite excited, of course, but there's a ton of paperwork to do—along with the need for gobs of signatures....More
May 8, 2013
blog

Use SSIS for ETL from Hadoop

In this blog post, Mark Kromer walks you through using SSIS as a way to use ETL techniques using Microsoft's Hadoop on Windows (HDInsight) as a source using Hive connectors...More
Vision road sign
May 6, 2013
blog

Cheaters Never Win, Even in TPC Benchmarks

In this portion of the series on database benchmarking, I want to tell you about one of my favorite aspects of the TPC benchmarks – CHEATING....More
SQL Server Pro Forums

Get answers to questions, share tips, and engage with the SQL Server community in our Forums.

SQLMag.com
Related Sites
Copyright © 2013 Penton Media, Inc