将 Oracle 与 SQL Server 连接起来
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3444576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linking Oracle with SQL Server
提问by bokoxev
I'm trying to link SQL Server 2005 to an Oracle 10g database. I've installed the Oracle client on the SQL server and validated that I can connect to the Oracle database using both tnsping and sqlplus. When I try to run a query in SQL Server I get the following:
我正在尝试将 SQL Server 2005 链接到 Oracle 10g 数据库。我已经在 SQL 服务器上安装了 Oracle 客户端，并验证了我可以使用 tnsping 和 sqlplus 连接到 Oracle 数据库。当我尝试在 SQL Server 中运行查询时，我得到以下信息：
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD".
Any ideas? I've tried both of the following queries with no luck:
有任何想法吗？我已经尝试了以下两个查询，但都没有运气：
select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent')
select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent')
select count(rowid) from ORA_CSSA2APD..eservice_op.agent
select count(rowid) from ORA_CSSA2APD..eservice_op.agent
采纳答案by Gary Myers
I suspect an environment setting. That is, your session is picking up the TNSNAMES.ORA file but the session underlying SQL Server is not. I'd check were ORACLE_HOME and, possibly, TNS_ADMIN are being set and pointing to.
我怀疑环境设置。也就是说，您的会话正在获取 TNSNAMES.ORA 文件，但 SQL Server 基础会话没有。我会检查 ORACLE_HOME 和 TNS_ADMIN 是否正在设置并指向。
Are you able to use the easy connect syntax for the database with the SQL Server connection .
您能否将数据库的简单连接语法与 SQL Server 连接一起使用。
IE replace ORA_CSSA2APD with hostname:1521/service_name
IE 将 ORA_CSSA2APD 替换为主机名：1521/service_name
回答by Patrick Marchand
ORA:12154 generally means that the alias of the db you're trying to connect to wasn't found in the tnsnames.ora file. (See http://ora-12154.ora-code.com/a more detailed explanation.)
ORA:12154 通常意味着在 tnsnames.ora 文件中找不到您尝试连接的数据库的别名。（参见http://ora-12154.ora-code.com/更详细的解释。）
You need to make sure that the Data Source is an alias that the tnsnames file knows about (on the server where SQL Server resides, regardless of where you're running the queries from); SQL Server is going to be just like any other Oracle client and needs to know where to connect to and without the tnsnames.ora file, it's not going to know the details of where the Oracle db is.
您需要确保数据源是 tnsnames 文件知道的别名（在 SQL Server 所在的服务器上，无论您从何处运行查询）；SQL Server 将就像任何其他 Oracle 客户端一样，需要知道连接到哪里以及没有 tnsnames.ora 文件，它不会知道 Oracle 数据库在哪里的详细信息。
If you don't have access to the SQL Server server (there's one from the department of redundancy department), you'll need to get the server admin to set that up for you.
如果您无权访问 SQL Server 服务器（冗余部门有一台），则需要让服务器管理员为您进行设置。
(The Data Source property of the linked server should be the alias in tnsnames.ora alias for the db you're trying to link to.)
（链接服务器的数据源属性应该是您尝试链接到的数据库的 tnsnames.ora 别名中的别名。）
HTH...
哈...
回答by Gary Myers
TNS error messages generally means the connection is flawed (eg host is unobtainable/timesout on the specified port, or that is simply doesn't know what ORA_CSSA2APD is supposed to point to).
TNS 错误消息通常意味着连接有缺陷（例如，主机在指定端口上无法获取/超时，或者根本不知道 ORA_CSSA2APD 应该指向什么）。
One thing to consider is, are you using a 64-bit Windows and are you using a 32-bit or 64-bit Oracle client (or possibly both). If you've got a 32-bit app running on a 64-bit OS trying to call Oracle, it needs a 32-bit Oracle client. Using a 32-bit client on a 64-bit OS can be tricky and it is safer to NOT install it in the "Program Files (x86)" folder.
需要考虑的一件事是，您使用的是 64 位 Windows 还是使用 32 位或 64 位 Oracle 客户端（或可能两者都使用）。如果您有一个在 64 位操作系统上运行的 32 位应用程序试图调用 Oracle，则它需要一个 32 位 Oracle 客户端。在 64 位操作系统上使用 32 位客户端可能会很棘手，最好不要将其安装在“Program Files (x86)”文件夹中。
Also bear in mind the following
还要记住以下几点
In a 64-bit version of Windows Server 2003 or of Windows XP, the %WinDir%\System32 folder is reserved for 64-bit applications. When a 32-bit application tries to access the System32 folder, access is redirected to the following folder: %WinDir%\SysWOW64
在 64 位版本的 Windows Server 2003 或 Windows XP 中，%WinDir%\System32 文件夹是为 64 位应用程序保留的。当 32 位应用程序尝试访问 System32 文件夹时，访问将重定向到以下文件夹：%WinDir%\SysWOW64
So for 64-bit windows, the 32 bit stuff is in the SysWOW64 folder and the 64 bit stuff is in the system32 folder.
所以对于 64 位 Windows，32 位的东西在 SysWOW64 文件夹中，64 位的东西在 system32 文件夹中。
回答by KirstieBallance
This issue happened to me, as well, but only with certain Windows user accounts. A combination of enabling the "Allow inprocess" provider option for the OraOLEDB.Oracle provide (SSMS > Server Objects > Linked Servers > Provides > OraOLEDB.Oracle), restarting the SQL Server Windows service and lastly adjusting the permissions on the TNSNAMES.ora file directly.
这个问题也发生在我身上，但仅限于某些 Windows 用户帐户。组合启用 OraOLEDB.Oracle 提供的“允许进程内”提供程序选项（SSMS > 服务器对象 > 链接服务器 > 提供 > OraOLEDB.Oracle），重新启动 SQL Server Windows 服务并最后调整 TNSNAMES.ora 文件的权限直接地。
回答by MyITGuy
We found that SQL Server, for some unknown reason, started looking for the TNSNAMES.ORA file in default Oracle locations.
我们发现 SQL Server 出于某种未知原因开始在默认的 Oracle 位置查找 TNSNAMES.ORA 文件。
We were able to place the TNSNAMES.ORA files in the following locations, with successful results:
我们能够将 TNSNAMES.ORA 文件放置在以下位置，并取得了成功：
For SQL Server 32-bit on 32-bit OS or 64-bit on 64-bit OS
对于 32 位操作系统上的 32 位 SQL Server 或 64 位操作系统上的 64 位 SQL Server
%ProgramFiles%\Oracle
%ProgramFiles%\Oracle\network\admin
For SQL Server 32-bit on 64-bit OS
对于 64 位操作系统上的 SQL Server 32 位
%ProgramFiles% (x86)\Oracle
%ProgramFiles% (x86)\Oracle\network\admin
We too were able to connect using SQL*Plus, but SQL Server could not. It is important to note that SQL*Plus did not use the same TNSNAMES.ORA file. This is why one worked.
我们也可以使用 SQL*Plus 进行连接，但 SQL Server 不能。需要注意的是，SQL*Plus 没有使用相同的 TNSNAMES.ORA 文件。这就是为什么一个工作。
