Skip to content

Installing 64-bit Oracle ODAC 11.2 to Microsoft SQL Server 2008 R2 x64

January 20, 2011

1. Download “64-bit ODAC (Oracle Data Access Components) 11.2 Release 3 (11.2.0.2.1) for Windows x64 [Released December 28, 2010]” 

2. Unzip to “C:\Temp\ODAC11201x64” folder of SQL Server.

3. Create Oracle home directory: C:\Oracle\ODAC11201x64

4. Open a command prompt as Administrator and cd to “C:\Temp\ODAC11201x64”

5. Execute the following:

 install.bat all c:\oracle\ODAC11201x64 ODAC11201x64

6. Once install completed, check C:\Oracle\ODAC11201x64\Install.log file to see if there were any errors.

7. Download “instantclient-sqlplus-win-x86-64-11.2.0.1.0.zip” and unzip to Temp folder.

8. Copy three files (sqlplus.exe, sqlplus.sym,Orasqlplusic11.dll) into “C:\Oracle\ODAC11201x64” folder.

9. Add a system environment variable “ORACLE_HOME=C:\Oracle\ODAC11201x64” and add two paths to system path: “C:\Oracle\ODAC11201x64” and “C:\Oracle\ODAC11201x64\bin”.

10. This is a critical step that was not mentioned in Oracle README file. You have to add two TNS files (sqlnet.ora, tnsnames.ora) which specifies how Oracle DLLs link the TNS alias and the actual Oracle server. At this point, you can check the connection by entering folowing command in a command prompt:

 cd c:\oracle\odac11201x64

sqlplus anoracleaccountname/password@oracleTNSname

Also, copy those two TNS files into “C:\Oracle\ODAC11201x64\Network\Admin\” folder. Even if SQL*PLUS can connect without this additional folder structure, when you try to set up an SQL Server linked server to Oracle, you may get the following error: “Cannot initialize the data source object of OLEDB provider “OraOLEDB.oracle” for linked server ‘linkedservername’ … ORA-12154: TNS:could not resolve the connect identifier specified” (Microsoft SQL Server, Error: 7303)

11. Run RegEdit and change HKLM:SOFTWARE:Oracle:KEY_ODAC11201x64:OLEDB FetchSize to 1000 (or whatever you want) from 100. This may not a necessary step but I’ve heard that this might cause an issue. Not sure though.

12. Restart SQL Server 2008 service (MSSQLSERVER if default instance). Without restarting the database engine service, the newly installed Oracle ODAC will not work. 

That’s pretty much about it.

Advertisements
2 Comments leave one →
  1. Kel permalink
    March 4, 2011 1:58 PM

    Nice one mate – worked perfectly – cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: