Connecting to Oracle from SQL Server

          source : http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you”ll have to install some software on your server to make things work. I”ve put together a short guide on connecting to Oracle from SQL Server. The process doesn”t take very long but there are several hoops to jump through.

1. Install Oracle Database 10g Client Release 2
   1. Install using the InstantClient option
2. Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
   1. Select the Oracle Data Access Components option (not .NET!)
3. Edit TNSNAMES.ora
   1. Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
   2. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):

      DMDEV =
       (DESCRIPTION =
       (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
       )
       (CONNECT_DATA =
       (SERVICE_NAME = FRIENDLYNAME)
       )
       )

      There are a couple of things you need to change:
      • HOST = SERVERNAME. The casino online real money value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
      • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
4. REBOOT!
5. Configure provider in SQL Server

   

   OraOLEDB.Oracle Provider Menu Item

   1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”
   
6. Create a linked server to the Oracle Database
   1. General
      1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
      2. Provider: Oracle Provider for OLE DB
      3. Product Name: “Oracle” is fine here
      4. Data Source: This should match the HOST you defined in TNSNAMES.ora

      

      Create a Linked Server

   2. Security
      1. Select Be made using this security context and supply the remote login and password
7. Query the linked server:

   SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP