SQL Server 2012与Oracle的连接 [英] SQL Server 2012 connectivity to Oracle
问题描述
我正在尝试将数据从Oracle复制到SQL Server 2012，并且在选择数据源作为Oracle的Microsoft OLE DB提供程序时收到以下消息:
I am trying to copy data from Oracle to SQL Server 2012, and I get the following message when selecting Data Source as Microsoft OLE DB Provider for Oracle:
由于初始化提供程序时出错，测试连接失败. 找不到Oracle客户端和网络组件.这些组成部分 由Oracle Corporation提供，并且是Oracle版本的一部分 7.3.3或更高版本的客户端软件安装.
Test connection failed because of an error in initializing provider. Oracle client and network components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
我尝试使用适用于Oracle的.NET Framework数据提供程序，并且得到:
I tried using .NET Framework Data Provider for Oracle and I get:
试图加载Oracle客户端库时抛出BadImageFormatException. 当在32位的64位模式下运行时，会出现此问题 已安装Oracle客户端组件(system.data.oracleclient).
Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed (system.data.oracleclient).
在SQL Server 2000(我正尝试移至SQL Server 2012/2014)中，我可以选择直接在OraClienthome中选择Oracle.
In SQL Server 2000 (which I am trying to move to SQL Server 2012/2014), I have the option of selection Oracle in OraClienthome directly.
一些可能有助于诊断问题的其他信息:
Some additional information that may help diagnose the problem:
-
使用Toad 64位，它指向64位Oracle下载；但是，我无法确定它是使用32位驱动程序还是64位驱动程序.我也可以运行查询等，而不会出现问题.
Using Toad 64 bit, it points to the 64 bit Oracle download; however, I can not tell if it is using a 32 bit driver or 64 bit driver. I can also run queries, etc. without issue.
ODBC，当我在OraClienthome中建立一个名为Oracle的32位ODBC连接(我正在猜测)时，可以看到Oracle，但是在SysWOW64 odbc连接中却看不到Oracle.
ODBC, I can see Oracle when making a 32 (I am guessing) bit ODBC connection named Oracle in OraClienthome, but not in SysWOW64 odbc connection.
我已在运行64位SQL Server 2012的服务器上成功建立了链接服务器连接.OraOLEDB.oracle显示在服务器对象Linked_Server提供程序下
I successfully established a linked server connection on a server running 64 Bit SQL Server 2012. OraOLEDB.oracle shows up under Server Objects Linked_Server Providers
在创建SSIS软件包时，我无法建立与Oracle的连接.
While creating an SSIS package, I am unable to establish a connection to Oracle.
我可以在MS Access和Excel中成功运行查询.
I can successfully run queries in MS Access and Excel.
(新)我可以使用64位而不是32位的Import Export Data复制文件.
(NEW) I can copy files using Import Export Data 64 bit, but not 32 Bit.
任何帮助将不胜感激！
推荐答案
在许多情况下，64位驱动程序不兼容，因此您必须安装32位驱动程序，然后确保选择使用32位驱动程序.创建一个作业来运行程序包，该程序位于命令选项中的最后一个复选框中.我还使用蟾蜍从台式机上的oracle中提取数据，它可以在64位驱动程序上正常工作，但是在新服务器上，即使我能够使用连接管理器创建odbc连接，我也必须安装32位驱动程序.还必须在安装后重新启动Win Server，然后才能使用它.
In many cases the 64 bit drivers are not compatible and you have to install the 32 bit drivers then be sure you are selecting to use the 32 bit drivers if you create a job to run the package which is in the command options as the last check box. I also use toad to pull data from oracle on my desktop and it works fine with the 64 bit driver but on our new server I had to install the 32 bit drivers even though I was able to create odbc connection with connection manager. Also had to reboot win server after instal before I could get it to take.
这篇关于SQL Server 2012与Oracle的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
