SQL Server 2014 64-bit version links 32-bit Oracle Database

Issue background:

New Bi, who has just entered the company, recently received a handover project from the department that needs to get data from the Oracle database on SQL Server via OpenQuery. Various configurations, various settings toss for a week long. Here, the problem solving process to bring out to share with you. Here need to emphasize a little, although the network resources are strong, but everyone's problem must be specific, avoid mechanically.

System configuration: Windows Server r2,64bit Intel Xeon 8 THREADS,48GB Memory;

Pre-installed software: VS. 32bit,sql Server 64bit,oracle Client 11g 32bit, where all drive characters and their sub-files have been set SQL server\agent have read-write and executable permissions.

Under the above conditions, the linked server in SSMS cannot display the Oracle home driver in PROVIDER,ODBC. Therefore, the tables of the Oracle database cannot be manipulated directly through OPENQUERY.

Solution:

The resolution of the problem began a week ago.

At first, my information retrieval ability is poor, for a long time did not find effective information. Basic information is found in the three communities of Oracle Community, MSDN, StackOverflow, and more. But the test did not work. One of the posts said that the problem was solved after installing the 64bit Oracle database, but I don't know why.

Next, a colleague encountered the same problem, I told him, I heard of the database solution but did not try. Next, he installed database on work station and successfully linked the Oracle database through OPENQUERY in SSMs. But when I wanted to pretend, he said he wasn't sure which version of the database he was in. He was confirmed to be 32bit and on the 2008 version of SSMs. Next, he recommended that I install the 32bit database test first.

1) Install 32bit Oracle Database (FAILED)

I first tried the 32bit database in the company's software library, and there was no response in SSMS or ODBC after the installation was successful. On the internet and through the search, what system32 folder DLL file check, registry modification has done, no effect. The final conclusion, 32bitssms-->32bitoracle,64bitssms-->64bitoracle, so I do not understand why the colleague is 32bit.

Then, I'll uninstall the Oracle 32bit database ... Various pits ... (in accordance with the online full uninstall to do)

2) Install 64bit Oracle database (cannot install)

The registry needs to be removed in accordance with the online uninstallation process, but since Oracle's registry series is common to the 32bit client side and database, the client side is not available after the uninstallation is complete. The built-in SSIS package is not available, and the 32bit Oracle provider fails. So, you have to re-install 32bit client side ...

The common problem with Oracle 11g installation package is that it cannot be compatible with Win7 and above, so adjust the Setup.exe file to Vista compatibility mode. Finally, the installation succeeds. The 32Bit Oracle provider can also be used normally after testing. Then, install the 64bit database.

The new problem has come up again! Database installation package setup.exe after the black box appears, then there is no response!!! This is the case regardless of whether compatibility mode is set. Next, test the 64bit client side, 32bit database, 32bit client side, 64bit Instant client, 32bit Instant client have similar situation, it is extremely depressing! Finally, back to the method used a long time ago, Odac 64bit Xcopy, however there is no egg to use, the command prompt installs ODAC unexpectedly access denied.

3) Connect, install 64bit client terminal at command prompt

At this point, I was very frustrated, searching for solutions on the Internet, but also disappointing ... Online suggestions for the general reload system, disk space size problems, reload 32bit SSMS, question and answer no results. However, the above solution does not apply because the server involves more users and I have Dickens to deploy the existing job to ssms successfully. Inadvertently, see Blog Park Xiaoping about 64bit SSMs via ODBC link 32bit Oracle posts (http://www.cnblogs.com/yuanxiaoping_21cn_com/archive/ 2013/11/20/3433020.html), let me rekindle hope. It says to install both the 32bit and 64bit client side, then modify the registry, build an ODBC DSN, and link in SSMs with ODBC. Of these, the two client side is installed at the same time, and my case is already installed 32bit.

Let's try the 64bit client side. Then, open the installation package, setup.exe a flash over ... I instantly petrified, as if back to the beginning of the problem, always unable to enter the universal installation interface. This is also true for setting compatibility mode. However, I decided to try to start the. exe file from a command prompt. First, disable all compatibility modes and then install the CD to the installation folder at the command prompt. The UI interface came out, and I was so excited this moment.

4) Modify the configuration file to change the compatibility

In the happy time, installation detection, the system version is not compatible. I set the compatibility and then opened it with a command prompt, and the results flashed through. Then I was desperately searching for Oracle installation, compatibility settings issues. One blog Park post mentions that when you install database, you can add the current system version to the configuration file Xxx.xml, which can be detected by the system. I think the client side must also exist such files, sure enough, I found.

First, go to the decompression after the installation package path, enter the \client\stage\prereq directory, locate the Prerequisite.properties profile, open. The following information is available:

Prerequisite_input=client/client_prereq.xml
Rulemap=oui/rulemap.xml;common/rulemap.xml
Knowledgesource=oui/knowledgesrc.xml
Reffiles=client/refhost.xml;client/refhost_instantclient.xml
Javalibs=oui/oraprereqchecks.jar; Client/clientprereq.jar

Each line equals the equivalent of pointing to a specific profile location, down, checking the system version of the configuration file most likely in the black marked line. But it's better to look for one. According to the first line, guess the file should be in the \client directory, and then find the \client\stage\prereq\client directory is really client_prereq.xml, and then open the check. Find the Refhost.xml folder in this section

<CERTIFIED_SYSTEMS>
<OPERATING_SYSTEM>
<!--Microsoft Windows 2000-->
<version value= "5.0"/>
<service_pack value= "1"/>
</OPERATING_SYSTEM>

</CERTIFIED_SYSTEMS>

So, I add a record to my system version,

<OPERATING_SYSTEM>
<!--Windows Server r2-->
<version value= "6.2"/>
<service_pack value= "1"/>
</OPERATING_SYSTEM>

Then, install the Setup.exe file with the command prompt, and the results are installed successfully.

5) Configure ODBC, configure SSMS Linked Server (Success)

Finally, the driver of Oracle HOME1 can be found in 64bit ODBC, so the ODBC test connection succeeds in modifying the TNS file in the Oracle Client installation directory.

In SSMs, create a new linked Server, select the Oracle Provider for OLE DB directly, enter the appropriate configuration, enter the user name, password in the security option, and test successfully. Modify the configuration of the Oraoledb.provider in the linked server, select Allow in process, and use the OPENQUERY test to succeed. It is not affected if the SSIS package and the deployed agent job have been tested back in the original 32bit.

At this point, 64bit SSMs connects and gets all of the 32bit Oracle data successfully ended.

SQL Server 2014 64-bit version link 32-bit Oracle database