Installing 64-bit Oracle ODAC 11.2 to Microsoft SQL Server 2008 R2 x64
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-188.8.131.52.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.