Monday, May 14, 2012

How to connect Oracle from SQL Server Management Studio

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.

  1. Install Oracle Database 10g Client
    • Select the Custom Installation
    • Select the following under available products components
      • Oracle Database utilities
      • SQL * Plus
      • Oracle windows Interfaces
        • Oracle ODBC Driver
        • Oracle Provider for OLEDB
        • Oracle Data Provider for .NET
      • Oracle Net
  2. At the point where we have to do the configuration, enter the following information
    • Service Name (may not be service name, but it is the first box that you type something in) – enter ORADB1 (for example)
    • Then select TCP
    • In the next box enter you Oracle DB server name where you want to connect.
    • Change the port to 1501 (for example)
    • If there is a textbox for Net Service name - it is ORADB1 (for example)
  3. REBOOT
  4. Open SQL Server management Studio and right click on “OraOLEDB.Oracle” under Server Objects –> Linked Servers –> Providers and Select Properties. Here Enable “Allow inprocess”
  5. Right click on Linked Server and select “New Linked Server”. Enter the following
    • Linked Server : A name of your choice
    • Provider : Oracle Provider for OLE DB
    • Product Name : A name of your choice
    • Data Source : This must match the Host you defined in TNSNAMES.ora which you can find in C:\oracle\product\10.2.0\client_2\NETWORK\ADMIN (mine is client_2, yours might be different).
    • Under Security tab, Select “Be made using this security context” and enter user name and password to connect oracle db.

 

Now I can see the Oracle data into SQL Server management studio and able to query if I want to.

SELECT * FROM [LINKED SERVER NAME]..[NAME Appears before Table name].[TABLE NAME]