Registering "Oracle Provider for OLE DB" and creating linked Servers

Create a linked server on the SQL Server database, connect to the Oracle database, and the provider needs to be set to: "Oracle Provider for OLE DB".

If this driver is not available on your computer, installing a complete ORACLE11GR2 program is a valid method, but this 64-bit version of the installation package has 2 multi-g,

quite large, if only to establish a linked server, there is absolutely no need to install such a monster. In fact, we only need to download ODAC compressed package .

The following summarizes how to register: "Oracle Provider for OLE DB" this driver:

Part1. registering "Oracle Provider for OLE DB" ":

1. Download Odac112030xcopy_x64.zip:

: http://download.csdn.net/detail/u014019244/9061209

2. Unzip the Odac112030xcopy_x64.zip (I unzipped to the C drive):

3. Run the Install.bat OLE DB c:\oracle ODAC installation after entering the decompression folder on the command line interface:

4. In the system environment variable, add "C:\oracle" for Path; C:\oracle\bin; "

(Computer Properties-Advanced system Settings-advanced-environment variable-system environment variable-path):

Select "Path" in "System variables" in "Environment variables", click "Edit" button,

Add "C:\oracle" at the front; C:\oracle\bin; "

5. Restart the computer.

After you have done this, open the native SQL Server Management Studio, and when you create a new linked server,

The "Access interface" drop-down box appears "Oracle Provider for OLE DB".

Part2. new Linked server:

1. Open native SQL Server Management Studio to connect to the native database;
2. Expand "Server Objects";
3. Select "Linked Server"-right-"new linked server";
4. In the "New Linked server" window, select "General";
5. Enter the "Linked server name" (the English character name for the test);
6. "Server Type" select "Other data source";
7. "Provider" select "Oracle Provider for OLE DB";
8. "Product name" input "Oracle";
9. Data source input: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.xxx.xxx) (PORT = 1521)) (Connect_data = (SERVER = Dedicate D) (service_name = ORCL)))

Where host is the IP of the server where the Oracle database resides;

10. In the "Security" screen, tick "Use this security context to establish a connection", enter "remote login" and "Use Password" (Oracle's login name and password):

One. Thesettings for the "Server Options" interface are as follows:

The query tests whether the linked server was created successfully, such as:

SQL Server database uses a linked server to query the syntax of an Oracle database:

SELECT * from OPENQUERY (swapdb, ' Select xxx from table name ')

Registering "Oracle Provider for OLE DB" and creating linked Servers