by Ellen
6. October 2009 22:48
Back in fall of 2006, Perkins Consulting embarked on a data warehousing project with a client whose source data resided in Oracle and whose SQL Server 2005 environment was established on a 64-bit server. This was our first experience with this particular combination of variables, and we ran into some entertaining issues.
I was attempting to set up a Connection Manager for the Oracle source database, using the Native OLE DB\Oracle Provider for OLE DB. When testing the connection, I received the following error:
Entertaining? Oh, yeah. A laugh riot.
After extensive Google searches, I finally found a post regarding this BIDS error in reference to Analysis Services. Apparently the issue was on the Oracle side. The Oracle provider had an issue with special characters in path names. The default installation folder for SQL Server 2005 32-bit executables (including BIDS) on a 64-bit server includes parentheses (i.e., c:\Program Files (x86)\…). Oracle was not pleased with this situation in the slightest.
The post I found recommended installing the 32-bit executables in a folder without special characters, but that wasn’t an option at our client site. The post provided a work-around using a batch file to launch BIDS and to explicitly set the executable path without using the special characters, placing the Oracle client first:
Set path= c:\oracle\product\10.2.0\client_1\bin;%path% "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"
The error only arises when calling the Oracle provider using BIDS (a 32-bit application). When executing the completed package, SQL Agent invokes the 64-bit dtexec executable, which is located in a path that TNS does not find objectionable.
It’s entirely possible that this issue has been resolved in later versions of the Oracle client or the native Oracle OLE DB provider, but because of the configuration at this particular client site, I still launch BIDS there using this batch file.
Subscribe