将SQL Server链接到Oracle数据库

最新推荐文章于 2024-08-06 14:51:02 发布

culuo4781

最新推荐文章于 2024-08-06 14:51:02 发布

阅读量1.2k

点赞数 1

文章标签：数据库 python linux java 编程语言

原文链接：https://www.sqlshack.com/link-sql-server-oracle-database/

版权

There are some cases in the wild where we want to get back data from an external database without additional coding in an application and just write a T-SQL query that will do all the job for us. That’s why Microsoft provides the « linked server » feature.

在某些情况下，我们希望从外部数据库获取数据而无需在应用程序中进行额外编码，而只是编写一个T-SQL查询即可为我们完成所有工作。这就是Microsoft提供“链接服务器”功能的原因。

But linked servers are, by default, unable to connect to an Oracle Database. This is the subject that will be treated here. We will see how to install the mandatory libraries and tools then we will configure the network layer of Oracle database (which is called the Oracle Net) to finally be able to create a linked server and query the target database instance.

但是，默认情况下，链接的服务器无法连接到Oracle数据库。这是将在这里处理的主题。我们将看到如何安装必需的库和工具，然后将配置Oracle数据库的网络层（称为Oracle Net）以最终能够创建链接服务器并查询目标数据库实例。

Let’s get started!

让我们开始吧！

安装Oracle数据库客户端 (Installation of Oracle Database Client)

First of all, stop the SQL Server instances running on the computer on which you will install. The installation won’t fail if instances are running, but at first call of the Linked Server Provider, the instance will hang and won’t respond!

首先，停止要在其上安装的计算机上运行SQL Server实例。如果实例正在运行，则安装不会失败，但是在首次调用Linked Server Provider时，实例将挂起并且不会响应！

In my case, I want to connect an Oracle Database 11g instance. So I will install an Oracle Database 11g client, but you can also try installing the latest version.

就我而言，我想连接一个Oracle Database 11g实例。因此，我将安装Oracle Database 11g客户端，但是您也可以尝试安装最新版本。

Source files can be downloaded on Oracle’s website. It’s in the form of a ZIP file. Download it, extract it and open the extracted folder.

可以在Oracle网站上下载源文件。它采用ZIP文件的形式。下载它，解压缩并打开解压缩的文件夹。

Once done, just click on « setup.exe »

完成后，只需单击«setup.exe»

This will open the following dialog. Choose « Custom »

这将打开以下对话框。选择“自定义”

Choose the appropriate languages that your client may use.

选择您的客户可以使用的适当语言。

Then choose the destination folder for your installation. I personally prefer separate it from operating system partition, and I follow the conventional path in Oracle as stated on the documentation page about Oracle Flexible Architecture (OFA).

然后选择安装的目标文件夹。我个人更喜欢将其与操作系统分区分开，并且遵循Oracle的常规路径，如关于Oracle灵活体系结构（OFA）的文档页面所述。

Let’s now select the appropriate components.

现在让我们选择适当的组件。

Database Utilities, SQL*Plus and Oracle Call Interface are selected in cases where one would add an external development for any reason. They may not be useful.
如果出于某种原因会添加外部开发，则选择数据库实用程序，SQL * Plus和Oracle调用接口 。它们可能没有用。
Oracle Net is the network layer of Oracle Database. This will allow us to configure « routes » in a file called tnsnames.ora
Oracle Net是Oracle数据库的网络层。这将使我们能够在名为tnsnames.ora的文件中配置“路由”
Oracle ODBC Driver, Oracle Provider for OLE DB and Oracle Provider for .NET are selected to allow SQL Server to contact the Oracle Database by different means.
选择了Oracle ODBC驱动程序，用于OLE DB的Oracle提供程序和用于.NET的Oracle提供程序，以允许SQL Server通过其他方式与Oracle数据库联系。

By clicking on the « Next » button, you will have to wait a little bit for verifications. Once they succeed, you will see the following recap :

通过单击“下一步”按钮，您将需要等待一点时间进行验证。一旦成功，您将看到以下概述：

Click on « End » and the installation begins…

点击“结束”，安装开始……

And we are done.

我们完成了。

Let’s just connect to the SQL Server instance and check that it sees the newly installed provider :

让我们仅连接到SQL Server实例，并检查它是否看到新安装的提供程序：

配置Oracle Net（添加对目标Oracle实例的引用） (Configure the Oracle Net (add reference to a target Oracle instance))

Location of configuration files

配置文件的位置

As we installed the Oracle software in I:\app\oracle, the network configuration files will be located in I:\app\oracle\product\11.2.0\client_1\network\admin.

当我们在I：\ app \ oracle中安装Oracle软件时，网络配置文件将位于I：\ app \ oracle \ product \ 11.2.0 \ client_1 \ network \ admin中。

Edit the configuration of Oracle Net (sqlnet.ora)

编辑Oracle Net（sqlnet.ora）的配置

The sqlnet.ora file is the profile configuration file. We will use it to first prioritize the name resolution method, to define the default domain and a time interval to verify that the connection is still active.

sqlnet.ora文件是配置文件配置文件。我们将使用它首先确定名称解析方法的优先级，定义默认域和时间间隔以验证连接仍处于活动状态。

Have a look at the following page for further information.

请查看以下页面以获取更多信息。

To do so, add the following lines in the sqlnet.ora file.

为此，请在sqlnet.ora文件中添加以下行。

 
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = LOCALDOM
SQLNET.EXPIRE_TIME = 30

Important note

重要的提示

When NAMES.DEFAULT_DOMAIN is defined, you will always have to use the fully qualified TNS name. You can remove it if you are not interested in using

定义NAMES.DEFAULT_DOMAIN后，您将始终必须使用完全限定的TNS名称。如果您对使用不感兴趣，可以将其删除

Edit the local list used in name resolution (tnsnames.ora)

编辑名称解析中使用的本地列表（tnsnames.ora）

A file that is very important in the configuration is the tnsnames.ora file. The definition provided by Oracle for this file is the following :

在配置中非常重要的文件是tnsnames.ora文件。 Oracle为此文件提供的定义如下：

This tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.

此tnsnames.ora文件是一个配置文件，其中包含映射为连接本地命名方法的描述符的网络服务名称，或映射为侦听器协议地址的网络服务名称。

Here is an example of content that will be used to create the linked server.

这是将用于创建链接服务器的内容示例。

 
 TESTSRV.LOCALDOM =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = TestSrv.st.chulg)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = TESTSRV.LOCALDOM)
   )
 )

Test nameing resolution

测试命名解析

Open cmd.exe and run the following command:

打开cmd.exe并运行以下命令：

 
 tnsping TESTSRV

You will get a text similar to the following:

您将收到类似于以下内容的文本：

创建到Oracle数据库实例的链接服务器 (Create a linked server to an Oracle Database instance)

Using SSMS

使用SSMS

The most accessible way to create a linked server is definitely via SQL Server Management Studio. To do so, connect to the SQL Server instance and find « Server Objects », one clicked, you will find « Linked Servers ». If you right-click on it, you will be able to create a new linked server.

创建链接服务器的最易用的方法肯定是通过SQL Server Management Studio。为此，连接到SQL Server实例并找到“服务器对象”，单击一下，您将找到“链接服务器”。如果右键单击它，则可以创建一个新的链接服务器。

Provide the information for a connection as follows. As Data source, you will use the name used in the tnsnames.ora file (with or without the domain name).

提供有关连接的信息，如下所示。作为数据源，您将使用tnsnames.ora文件中使用的名称（带或不带域名）。

In the security part of the configuration, depending on the configuration of your environment you will choose the appropriate method to use for remote authentication.

在配置的安全性部分，根据您环境的配置，您将选择用于远程身份验证的适当方法。

You can also review linked server settings to adjust according to your environment.

您还可以查看链接的服务器设置，以根据您的环境进行调整。

Once clicked on « OK », the linked server will be created and added in the « Linked Servers » list, as follows.

单击“确定”后，将创建链接服务器，并将其添加到“链接服务器”列表中，如下所示。

We can test the linked server. To do so, right click on it and choose « Test connection ».

我们可以测试链接服务器。为此，右键单击它，然后选择“测试连接”。

If everything is well configured, you will get the following dialog :

如果一切配置正确，您将获得以下对话框：

You can also list tables and views that you can query via by going down the tree view :

您还可以通过向下浏览树视图列出可以查询的表和视图：

Finally, you can run the following query :

最后，您可以运行以下查询：

 
  SELECT * FROM OPENQUERY([DBLNK_TEST] , 'select sysdate from dual') ;

For further information, follow the links below :

有关更多信息，请访问以下链接：

Alternative : my stored procedure

替代方法：我的存储过程

While SSMS is easy to use, I like automation. That’s the reason why I developed a stored procedure that does exactly the same, but in a scripted and faster way.

虽然SSMS易于使用，但我喜欢自动化。这就是为什么我开发出一种存储过程的功能完全相同，但是以脚本化且更快的方式进行的原因。

The stored procedure is called [Administration].[CreateStdLinkedServer]. This stored procedure just works with Oracle Linked Server, but will be updated in the future to handle every kind of linked server.

该存储过程称为[Administration]。[CreateStdLinkedServer]。此存储过程仅适用于Oracle Linked Server，但将来会进行更新以处理各种链接服务器。

It has the following interface :

它具有以下接口：

 
 [Administration].[CreateStdLinkedServer] (
    @LinkedServerName   SYSNAME,
    @ServerProduct      nvarchar(128),
    @DataSource         nvarchar(4000),
    @IdentityForwarding varchar(8) = 'true',
    @Username           SYSNAME,
    @Password           SYSNAME,
    @debug              TINYINT = 0
)

This procedure only works when @ServerProduct equals « Oracle ». It’s attached to this article and you can download it here

仅当@ServerProduct等于«Oracle»时，此过程才有效。它附带在本文中，您可以在此处下载

Here is the procedure call for our linked server DBLNK_TEST.

这是链接服务器DBLNK_TEST的过程调用。

 
  EXEC [dbo].[CreateStdLinkedServer]
    @LinkedServerName   = 'DBLNK_TEST',
    @ServerProduct      = 'Oracle',
    @DataSource         = 'TESTSRV',
    @IdentityForwarding = 'FALSE',
    @Username           = 'sdsq',
    @Password           = 'sdqsqs',
    @debug              = 1
;

故障排除 (Troubleshooting)

Sometimes, things can go wrong. You will find in this section problems and a way to address those problems.

有时，事情可能会出错。您将在本节中找到问题以及解决这些问题的方法。

TNS:could not resolve the connect identifier specified.
TNS：无法解析指定的连接标识符。

Don’t worry for the error, we should find a solution !

不用担心错误，我们应该找到解决方法！

Generally, you might end up with this error didn’t edit the tnsnames file appropriately, like in the example at “Edit the local list used in name resolution (tnsnames.ora)” section. This error means that there is no definition to the server you want to link.

通常，您可能会因未正确编辑tnsnames文件而导致此错误，例如“编辑名称解析中使用的本地列表（tnsnames.ora）”部分中的示例。此错误意味着您要链接的服务器没有定义。

I advise you to review the definitions in your tnsnames.ora file and make sure that tnsping gives you a “OK” status. If not, make sure that you can ping the address (IP or DNS) that is referenced as the host in the TNS entry.

我建议您检查tnsnames.ora文件中的定义，并确保tnsping为您提供“确定”状态。如果不是，请确保您可以ping通TNS条目中引用为主机的地址（IP或DNS）。

More info on “ORA-12154: TNS: could not resolve the connect identifier specified” error while creating a linked server to Oracle

创建到Oracle的链接服务器时，有关“ ORA-12154：TNS：无法解析指定的连接标识符”错误的更多信息