Solved

Link SQL Server to Oracle

Posted on 2014-03-28
6
662 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
always on switch back after failover 2 34
Alternative of IN Clause in SQL Server 3 21
access query to sql server 3 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

773 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