0

I have an Oracle 9i in my server and MS SQL Server 2017. I wanted to link the Oracle database to my MS SQL. I am trying to add the Oracle Database to MS SQL as a Linked Server.

Improve this question
8
  • Please explain further what link means to you? move data from a DB to other DB? how to migrate data?
    – igiannak
    Commented Sep 12, 2019 at 7:45
  • @GiannakopoulosJ I am trying inserting data to Oracle database through MS SQL
    – Lawrence Agulto
    Commented Sep 12, 2019 at 7:46
  • @GiannakopoulosJ or inserting data in my MS SQL Database and moving the data inserted to the Oracle database
    – Lawrence Agulto
    Commented Sep 12, 2019 at 7:47
  • There is a tool SQL Server Migration Assistant that allows you to migrate data from Oracle->MSSQL but you want vice versa. It seems that you have to create a mechanism of extract/load customized for your need.
    – igiannak
    Commented Sep 12, 2019 at 11:07
  • @GiannakopoulosJ I am trying to query insert data from MS SQL database to Oracle is that possible?
    – Lawrence Agulto
    Commented Sep 13, 2019 at 0:07

1 Answer 1

Reset to default
0

You can create an Oracle linked server on your SQL Server instance to query data on Oracle from SQL Server. Below are the steps you need to do:

  1. Install the Oracle 64-bit client software. You only need the barest install of the client.
  2. Once you have an installed 64-bit Oracle Home you can proceed with installing the ODAC drivers for a 64-bit environment. The OraOLEDB.Oracle driver installed in this step is what you need to set up your linked server. You'll find the setup.exe for this installation located in the unzipped Install folder.
  3. Open SQL Server Configuration Manager and restart SQL Server services.
  4. Configure OraOLEDB.Oracle provider. In SSMS (SQL Server Management Studio) Object Explorer, navigate down the tree of the SQL instance we've been working with until you're able to view the list of Providers under Linked Servers. Select OraOLEDB.Oracle and right-click.

enter image description here

The Provider Options dialog box appears. Enable the "Allow inprocess" option and close the dialog.

enter image description here

  1. Create Linked Server and Add Logins.

--Create the Oracle Linked Server: 

EXEC sp_addlinkedserver @server, @srvproduct, @provider, @datasrc

--For example: If I want to create a linked server called LK_TIPS to the ORATIPS --database on the SRVORA1 server, listening on the default port of 1521 my query --would look like this: 

EXEC sp_addlinkedserver 'LK_TIPS', 'Oracle', 'OraOLEDB.Oracle', 'SRVORA1:1521/ORATIPS'

Create a login for the LK_TIPS linked server we just created with a login of 'sa' and a password set to 'password' would look like this:

EXEC sp_addlinkedsrvlogin 'LK_TIPS', 'FALSE', 'sa', 'password'
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .