Create a linked server for Oracle in 64-bit SQL Server

Cross-Library queries are often used when we use SQL Server and Oracle to store data at the same time. To make it easier to use cross-Library queries, one of the best ways to do this is by creating a linked server. You can create a linked server for Oracle in SQL Server, or you can create a linked server for SQL Server in Oracle. This article focuses on how to create a linked server for Oracle in SQL Server, which can be created in both graphical and command ways.

Tools/Materials

• Microsoft Windows Server 2003 R2 Enterprise x64 Edition Service Pack2

• Microsoft SQL Server 2005 Standard Edition x64

• Oracle Provider for OLE DB

0. Preparatory work-Installation drive

• If you are using a 32-bit win2003 operating system, there will be a system-brought Microsoft OLE DB Provider for Oracle driver, so no additional preparation is required, but if you are using a 64-bit system, this driver is not, and Microsoft does not provide the 64-bit version of this driver, so only Oracle Provider for OLE DB is available at this time.

  So how do you install this driver? Installing a complete ORACLE11GR2 program is also an effective 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 no need to install such a monster. In fact, we only need to download the ODAC compressed package on the Oracle official website.

  As to how to install ODAC, not the focus of this article, you can search for my other article Baidu-"Oracle data Access Component ODAC installation method."

  END

1. Create an Oracle linked server from the graphical interface

1. 1

   Open Microsoft SQL Server Management Studio and log in as SA, expand the Server Objects node in the Object Explorer window on the left, and right-click on the Linked Servers node below to select New linked server ...

2. 2

   In the "New Linked server" dialog box that pops up, fill in the details as shown in the diagram below:

   * Linked server: This is the alias of the linked server, the name can be determined by your own discretion

   * Server Type Select "Other data Sources"

   * Access interface: Be sure to select "Oracle Provider for OLE DB". If you have a 32-bit system, you can also select Microsoft OLE DB Provider for Oracle, but the 64-bit system will only be able to select the former

   * Product Name: Must be "Oracle", this is a fixed wording, as long as the link Oracle database, all have to write

   * Data Source: Is the library name of the Oracle database you want to link to. In my Oracle database software, set up a database named Ora, I want to link to this database named Ora, so I here "data source" is filled with "ora", we can according to their actual situation to fill in.

   don't rush to click on the "OK" button when you are done.

3. 3

   Then click "Security" in the "Options page" on the left, select "Make a connection using this security context" on the right, and enter the user name and password of the database connected to Ora (change according to your actual situation) below. Then click on the "Confirm" button, at this point, a clear ora_test of the linked server is built.

   END

2. Create a linked server for Oracle by command

• Complete the following two command statements and then execute them in SQL Server to create the success:

• EXEC master.dbo.sp_addlinkedserver @server = N ' aliases ', @srvproduct =n ' library name ', @provider =n ' Msdaora ', @datasrc =n ' TNS name '

• EXEC Master.dbo.sp_addlinkedsrvlogin @rmtsrvname = n ' Alias ', @locallogin = NULL, @useself = N ' False ', @rmtuser = n ' pattern name ', @rmt Password = N ' password '

  END

3. Test Query Oracle Database

• SELECT * FROM OPENQUERY (ora_test, ' SELECT * from tab ')

  END

4. Issues that may be encountered

• When you test query an Oracle database, you may encounter an error that cannot create an instance of "OraOLEDB.Oracle". This issue can be found in the Baidu experience in my other article-"Unable to create a" oraoledb.oracle "example of the solution".

  END

Precautions

• The success of this article is based on the successful installation of the Oracle database driver and the correct configuration of the Tnsnames.ora file, if there is doubt, you can Baidu search my other article-"Oracle data Access Component ODAC installation method."

• When you test query an Oracle database, you may encounter an error that cannot create an instance of "OraOLEDB.Oracle". This issue can be found in the Baidu experience in my other article-"Unable to create a" oraoledb.oracle "example of the solution".

Create a linked server for Oracle in 64-bit SQL Server