Create a linked Server to Oracle View in MS SQL Server

Install the Oracle Client 

  1. Download the Oracle client 11gR2 (32/64 bit depends on OS) (Download)

  2. Install Oracle Client

    - Run the Setup.exe as Administrator, it will open the command prompt, wait until it finishes, (Normally it takes long time to finish)

            

    - Select Installation Type - Choose Administrator

- Select Product Languages 

- Specify Installation Location - leave it as default and note down the "Software location" - need to   refer that location later

- Check the summary and click "Finish".

  - Check the Installation Status

- Close the installation wizard once the installation is successful.

  3.  Modify the "tnsnames.oRA" file

      

    - Go the that Installation path (C:\app\User\product\11.1.0\client_4\network\admin\sample) 

    -  Copy (tnsnames.oRA )file in to the AdminFolder (C:\app\User\product\11.1.0\client_2 \network\admin)  

    -  Open "tnsnames.oRA" file , take the backup of that file before edit.

    -  Delete all except comments, and paste below entry

          

      VIRVHRMS =(DESCRIPTION =(ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = ###)(PORT = ###))) (CONNECT_DATA =(SERVICE_NAME = ###)))

•  VIRVHRMS - Data source Name (Give any name - you have to give this name in Oracle data connection)  
• You have to change some parameters according to your environment
• Host - Database Name
• PORT - Port Number
• SERVICE_NAME - SID 

- Finally Save that file 

  Create Linked Server with Oracle View in SQL Server 

  before create the Linked Server, enable the "Allow inprocess".

   Go to OraOLEDB.Oracle 's properties

  Enable "Allow in process"

   

OK, Now we can create a link server to Oracle View,

   1. Create New Linked Server

       

   2.Type the Name for Linked Server (PRODVIEW) and select "Other data source" for Server Type.

   

• Linked Server: “PRODVIEW”.  – Just a name 
• Provider: "Oracle Provider for OLE DB" 
• Product Name: “Oracle” 
• Data Source: “VIRVHRMS”   - The same entry in tnsname.ora file

3. Go to Security tab , and Select "Be made using this security context" and give the remote login  and password and click "OK".