Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

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.

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