• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Link SQL Server to Oracle

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
iamnamja
Asked:
iamnamja
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Walter RitzelSenior Software EngineerCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
iamnamjaAuthor Commented:
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
 
Walter RitzelSenior Software EngineerCommented:
The difference is BASIC Environment policies, for the sake of security or ease of tracking. If your environment allows dblink, go for it.
0
 
Walter RitzelSenior Software EngineerCommented:
How to create a oracle dblink to mssql:
http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now