TechNucleus

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]