Solved

Link SQL Server to Oracle

Posted on 2014-03-28
6
658 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
6 Comments
 
LVL 39

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 34

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 15

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 15

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 15

Accepted Solution

by:
Walter Ritzel earned 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 36
sql query 7 37
SQL Server memory Issue 7 76
Syntax using Declare 3 11
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now