Create an Oracle Link Server in 64-bit SQL Server

Create an Oracle Link Server in 64-bit SQL Server

Sometimes we want to access data from another SQL Server database or data from other Oracle databases under one SQL Server. to complete these operations, we must first create a database link.

The database link allows a local SQL Server login user to map to a remote database Server, just like operating a local database.

SQL Server Link Server
Use the Linked Server provided by ms SQL Server to establish one-way trust connections to other heterogeneous database servers for data transmission.
Distribution Transaction is supported, which has good performance, but has platform restrictions. The connection can only be set on the SQL Server side and is transparent.
The difference between SQL syntax and common methods is very small, which helps developers quickly adapt to use.

It is suitable for the use of SQL Server-based projects with multiple databases and data associations. It is transparent to use and does not need to be considered differently in development.
The impact of the database, and can provide comprehensive transaction support.

I. How to install the Oracle Data Access Component ODAC ----- install the driver

Note: The driver is installed to enable Oracle provider for old db for the data source in SQLSERSSVER. MSSQL2008 comes with this driver by default.

Fang Yi:
If the 32-bit win2003 operating system is used, there will be the Microsoft ole db Provider for Oracle driver that comes with the system, so no additional preparation is required, but if the 64-bit system is used, the driver is unavailable, and Microsoft does not provide the 64-bit version of the driver. Therefore, you can only use the Oracle Provider for ole db driver provided by Oracle.
Installing a complete Oracle11gR2 program is also an effective method. However, this 64-bit version of the installation package has two multi-G packages, which are quite large. If you only want to build a linked server, there is no need to install such a monster. In fact, we only need to download the ODAC package on the Oracle official website.
Fang Yi:
Http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
I downloaded ODAC 11.2 Release 4 and Oracle Developer Tools for Visual Studio (11.2.0.3.0)

This article takes the second

Party 2: Download 64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64. the installation package name is odac112040xcopy_64bit.zip.

1. decompress the zip package and decompress all the files in it to a folder. I decompress the package to D: \ ODAC112040Xcopy_64bit, open cmd, and enter the folder.
This folder contains eight subfolders:
Instantclient_11_2: This is the Oracle client. The running of all components depends on this client. It is the most basic
Oledb: This is an ole db Component.
Asp.net: This is an ASP. NET2 component.
Asp. net4: This is an ASP. NET4 component.
Odp. net4: This is the. NET4 component.
Odp. net20: This is the. NET2 component.
Oramts: This is Oracle's MTS Service
Network: this folder is used to store the tnsnames. ora file and can be used only after installation.

Dependencies between components. :

Instantclient_11_2 is the most basic
Oledb depends on instantclient_11_2
Asp.net depends on instantclient_11_2 and odp. net20
Asp. net4 depends on instantclient_11_2 and odp. net4
Odp. net4 depends on instantclient_11_2
Odp. net20 depends on instantclient_11_2
Oramts depends on instantclient_11_2

2. Install the ODAC package. (After installation, an Oracle provider for old db data source will be added to SQL SERVSER)

Run the install. bat batch file in cmd: Enter "install. bat all D: \ OracleClient ODAC" (without double quotation marks ). Note that three parameters can be input, but up to four parameters can be input.

The first parameter is all. All indicates installing all the components and clients in the ODAC112040Xcopy_64bit folder. If you don't need so many components, just use one of them. For example, if you only use the ole db Component, you can install only the client and the ole db Component as follows: install. bat oledb D: \ OracleClient ODAC

The second parameter is D: \ OracleClient. This is the installation path, which is specified according to your actual situation. Try not to include spaces and parentheses in the path.

The third parameter is ODAC. This parameter is called oracle home name. You can specify a string by yourself, not necessarily "ODAC ". This parameter is used to write data to the Registry. For example, after the preceding statement is executed, it will be written to the following location in the registry: HKLM \ Software \ Oracle \ KEY_ODAC. The ODAC after the "KEY _" is the "ODAC" you input in the parameter"

The fourth parameter. Generally, only the first three parameters are enough. When installing a component, all the components it depends on will be installed automatically. However, if you only want to install the specified component, the fourth parameter is used only when you do not want to install the components on which it depends. For example, if you only use the first three parameters when installing asp.net, the odp on which it depends will be automatically used. net20 and instantclient_11_2 are also installed, but if you input the fourth parameter "false", as shown below: install. bat asp.net D: \ OracleClient ODAC false then only asp.net will be installed instead of odp. net20 and instantclient_11_2 are also installed.

3. Set Environment Variables

ORACLE_HOME = D: \ OracleClient (add an environment variable ORACLE_HOME, whose value is your installation path. For example, I am D: \ OracleClient)

PATH = % ORACLE_HOME %; % ORACLE_HOME % \ bin; (Add the following two paths to the PATH environment variable, separated by semicolons: % ORACLE_HOME %; % ORACLE_HOME % \ bin ;)

2. Configure the tnsnames. ora file as needed

Install ORACLE 11 64-bit client

If you want to use components to access the Oracle database, configure the tnsnames. ora file as needed and store it in the % ORACLE_HOME % \ network \ admin directory. The format of my tnsnames. ora file is as follows:

NALGENE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.1.127) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = nalgene)
)
)

 

3. Create an Oracle linked Server in a 64-bit SQL Server

Note that the following data source is filled with the parameter name in the upper-left corner of the tnsname. ora file, instead of the name in the red box.

 

 

 

The running effect is as follows:

 

 

Create a sqlserver database link for Oracle:

Create view vvv as select * from TESTORACLE .. SCOTT. SYS_USER
Select * from vvv;
SCOTT is the username of the remote oracle database server, and SYS_USER is a table of the user on the server. Note that: Database Link (TESTORACLE) there are two dots (...) behind (..), the query object is generally a table or view and cannot be used to query synonyms.

-- 1. Configure the LinkServer statement as follows (the linked server is Oracle and the oracle oledb library MSDAORA is used)
-- 1.1 create a linked server