October 29, 2013 at 9:45 am
Installed the 11g Client on my Windows 7 (32 bit) and the Windows 2008 Server (64-bit).
TNSPing connects to report success and resolve the Oracle Service on both platforms.
Oracle Net Manager can successfully test postitive to the same Oracle Service on both platforms.
Windows Oracle ODBC - can connect - the TNSService Name autopopulates with many options. Shows test is positive.
Windows 2008 - ODBC FAILS after manually typing in a TNSService Name.
Question:
To set up a SQL Linked Service - will a Successful ODBC connection be necessary?
In other words, if ODBC won't connect, am I wasting time with the SQL Linked Server script?
This is an MSDN article about the error my ODBC receives -- However - it references the Microsoft ODBC - I have installed the Oracle 11g Client.
October 31, 2013 at 4:36 am
You need the OLEDB provider for Oracle in order to create a linked server.
The fact that ODBC doesn't work should be a clue that something is wrong, though.
Did you set up the ODBC on the correct bitness? As you probably know, ODBC has two different dialogs to manage x86 and x64 DSNs.
-- Gianluca Sartori
October 31, 2013 at 7:01 am
Yes, the 64 bit ODAC, I re-registered the DLL with the WOW-64 as many report the Oracle installer isn't dependable.
The Oracle Net Configuration Assistant and Net Manager both show successful login to the Oracle system as does the TSNPing utility.
The Oracle OLEDB shows up in the SQL Server Provider list.
Microsoft chose to use a one-error-fits-all approach to a failure.
November 4, 2013 at 7:44 am
Found an Oracle DBA. He validated that my registry had 2 installations. Caused a criss-cross of tools vs SQL Providers.
So, we deleted both of them and the directories, rebooted.
His approach was something I have not read before.
He created 2 directories
C:\Oracle\32bit
C:\oracle\64Bit
Then installed each tool in each of the directories. Suggested that this has prevented issues for various 3rd party tools.
The rest was following the script and the linked server worked.
From MS SQL Server Management Studio, I am surprised that expanding the tables and views takes over 30 seconds. However it is a VPN from our office half-way across the nation.
The bottom line is, don't install the Oracle Client 11g in the same root directory twice. If the installation fails, create a new directory.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply