Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Link SQL Server to Oracle

Posted on 2014-03-28
6
Medium Priority
?
719 Views
Last Modified: 2014-04-29
Hi,

I need to pull data from my SQL Server database to Oracle database.
Is there any easy way to connect the server/database from SQL Server to Oracle?

I use MS SQL Server Management Studio and Oracle SQL Developer (Version 3.2.20.09).

Thank you.
0
Comment
Question by:iamnamja
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39961895
Pretty easy - all you need is to install ORACLE client software on SQL Server and use it to create a Linked Server to ORACLE:


http://technet.microsoft.com/en-us/library/ms190618(v=sql.105).aspx
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39962071
If I understand the question correctly, you need the opposite of what lcohan suggested: a connection from the Oracle database to the SQL Server database.  Yes, the Oracle database software supports this.

Or, you can configure your SQL Developer to connect directly to a SQL Server database.

Which do you prefer, a "database link" from the Oracle database to the SQL Server database?  Or, do you just want a client connection to both?
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39962222
So, there are a lot of variables to be solved before give you an straight answer on how to do it. Both experts above came up with pretty valid questions to try to guide you.

Following that line, I would suggest, based on my experience with data warehouse, that you use a "middleware", meaning, a software that can connect on both servers and do the job for you with just a configuration file. On both sides, you have options for that. If this is a one thing only, SQL Server have data pump, I believe, and Oracle could do that with SQL Developer. If you need options that will run in a scheduled manner, you may want to consider SSIS on SQL Server side. I dont recall if SQL Developer can create some kind of package that could be scheduled on Oracle side.

I'm suggesting that because if you are in a corporate environment, maybe DB-links or linked servers are not options that DBAs like a lot, for security reasons, etc...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:iamnamja
ID: 39967639
Hi, Thank you for all comments.
I guess my question was a bit confusing.
I can go either way as far as the connection goes, but I have most of the data on the Oracle side, so I would prefer to have a connection from the Oracle database to the SQL Server database so that I can write queries in Oracle.

What's the difference between having the database link vs. client connection? I am in a corporate environment, but don't have any plans to create scheduled packages yet.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39967918
The difference is BASIC Environment policies, for the sake of security or ease of tracking. If your environment allows dblink, go for it.
0
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 2000 total points
ID: 39968975
How to create a oracle dblink to mssql:
http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question