Friday, May 29, 2015

Home » , , » How to connect to oracle database from sql server management studio

How to connect to oracle database from sql server management studio

1:33 AM  , ,  4 comments
Recently I am working with Oracle database and I want to see the list of tables and views. As we all know its not as friendly as SQL server. So I want to connect (Linked Server) the oracle to SQL SERVER for quick view of the data. Here are the steps I followed to setup a linked server. (I'm working with Oracle 12c and MSSQL 2008 R2 STD)

1. Install ODAC 12 (Oracle Data Access Components) Download: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

  • Extract file and run setup.exe


  • Select language > Next



  •  Select option: Use Windows built in Account


  •  Specify  an  Oracle base path to place all Oracle software and configuration-files.


  •  Select 
    • Oracle Data Provider for .NET
    • Oracle Providers for ASP.NET
    • Oracle Services for Microsoft Transaction Server
    • Oracle Data Access Components Documentation for Visual Studio

  • Next




  • Click Install


  • Complete


2. Reboot
3. Create linked server
  • Open SQL Server Management Studio (SSMS)
  • Right click on "OraOLEDB.Oracle" under Server Objects -> Linked Servers -> Providers and select Properties
  • Check to Enable "Allow inprocess"
  • Right click on "Linked Server" and select "New Linked Server..."

On page General:
- Linked server: <any linked server name> (ex: CONNECT_ORACLE)
- Server type: select Other data source
          + Provider: Oracle Provider for OLE DB
          + Product name: <any> (ex: OraOLEDB.Oracle)
          + Data source: //Server-IP:port/SID (ex: //10.0.0.8:1521/ORCL)
On Security page:
Select option: "Be made using this security context"
Type remote login username and password for oracle
Remote login: <username> (ex: oracleuser)
Password: <password> (ex: oracleuser)
4. Select oracle data from SSMS
SELECT * FROM [LINKED SERVER NAME]..[SCHEMA].[TABLENAME]
EX: SELECT * FROM CONNECT_ORACLE.[ORACLEUSER].[TEST]


4 comments:

  1. UnknownJanuary 8, 2018 at 4:48 AM

    Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.oracle training in chennai.

    ReplyDelete
  2. shinyMarch 6, 2019 at 8:24 PM

    Thank u so much for your clear description about database connectivities.Screenshots added value to ur blog.
    mobile service centre
    mobile service center in chennai
    mobile service center chennai

    ReplyDelete
  3. UnknownJune 27, 2019 at 1:29 PM

    I installed oracle 12c already,its working in sql plus.I am getting problem when im trying to connect to oracle db from ssms,I am not able to create connection meand getting errors.How can i resolve this?

    ReplyDelete
  4. UnknownOctober 2, 2020 at 12:32 AM

    Thanks for sharing this great information on Oracle PPM Cloud. Actually I was looking for the same information on internet for Oracle Project Portfolio Management (PPM) Cloud Tutorial and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about PPM Cloud by attending Oracle PPM Cloud Training .

    ReplyDelete