从SQL Server查询Oracle数据库

Question

我有一个Oracle 11g XE数据库，想要转移到SQL Server Express 2005。

起初，我想只需要将Oracle中的表生成为SQL格式，调整数据格式并在SQL Server上运行查询。但是这种方法仅适用于小表格，而我有几个表格包含成千上万行，甚至有些表格包含数百万行，因此这种解决方案行不通。

然后，我创建了一个包含以下内容的TNS文件：

OracleTnsName = 
(
  DESCRIPTION=
  (
    ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521)
  )
  (
    CONNECT_DATA = (SERVICE_NAME=XE)
  )
)

我按照其他地方找到的说明生成ODBC连接，'测试连接'成功。

然后我运行了以下命令在MS SQL中创建一个链接服务器：

EXEC sp_addlinkedserver 
     @server            = 'OracleLinkServer'
    ,@srvproduct        = 'OracleTnsName'
    ,@provider          = 'MSDASQL'
    ,@datasrc           = 'OracleTnsName'

EXEC sp_addlinkedsrvlogin 
     @rmtsrvname        = 'OracleLinkServer'
    ,@useself           = 'False'
    ,@locallogin        = NULL
    ,@rmtuser           = 'user'
    ,@rmtpassword       = 'password'

现在我正在尝试使用openquery从SQL Server查询Oracle数据库中的一张表：

select * from openquery(OracleLinkServer, 'select * from oracleTable')

但是出现了一个错误：

Msg 7399，级别16，状态1，行1
连接服务器“OracleLinkServer”时，OLE DB提供程序“MSDASQL”报告了一个错误。提供程序没有提供有关错误的任何信息。
Msg 7303，级别16，状态1，行1
无法初始化连接服务器“OracleLinkServer”的OLE DB提供程序“MSDASQL”的数据源对象。

当我检查链接服务器的属性并单击“确定”时，会出现以下错误：

标题：Microsoft SQL Server Management Studio Express

“已更新链接服务器，但未通过连接测试。是否要编辑链接服务器属性？”

---

其他信息:

执行Transact-SQL语句或批处理时发生异常。（Microsoft.SqlServer.Express.ConnectionInfo）

---

连接服务器“OracleLinkServer”时，OLE DB提供程序“MSDASQL”报告了一个错误。提供程序没有提供有关错误的任何信息。 无法初始化连接服务器“OracleLinkServer”的OLE DB提供程序“MSDASQL”的数据源对象。（Microsoft SQL Server，错误：7399）

如需帮助，请单击：http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

---

按钮:

&是

&否

请帮忙！

谢谢

- mikimr

---

欢迎来到StackOverflow：如果您发布代码、XML或数据样本，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s

驱动程序和SQL Server的位数是多少？如果您只安装了32位驱动程序，并且正在运行64位SQL Server，那么这可能就是原因。 - Nick.McDermaid

请确认您已在 SQL Server 上安装了 Oracle 客户端和 TNS 文件。然后，您需要使用 Oracle 工具（如 TNSPING 和 SQLPLUS）测试 Oracle 客户端。接下来，您需要确认位数是否匹配。我建议您不要这样做，而是将 Oracle 数据导出为平面文件，复制它并以此方式导入，因为 Oracle 驱动程序可能非常不友好。 - Nick.McDermaid

如果只选择一个单个列而不是使用 * 会发生什么？（最好像整数这样简单的类型）或者使用 WHERE 子句挑选出特定行会怎样？有时候，某个列中的坏数据可能会导致链接服务器代码出问题——我曾经看到过 Oracle 的某些日期超出了 SQL Server 的有效日期范围。 - Matt Gibson

Answer 1

如果您已成功添加了链接服务器，则不再需要使用OPENQUERY。您只需将链接服务器名称作为限定名称的第一部分即可，如下所示：

SELECT * FROM OracleLinkServer.database.schema.table

不确定您需要哪些部件，但点是关键。首先尝试这个：

SELECT * FROM OracleLinkServer...oracleTable

- David M

---

谢谢。我尝试了这种格式，但是还是出现了同样的错误信息。 - mikimr

你可以尝试这个：[OracleLinkServer]...[schema].[table]。 - StuartLC

Answer 2

更改

,@provider = 'MSDASQL'

使用

,@provider = 'MSDAORA'

- Al Fes

Answer 3

select * 
from [server]..[xxx].[yyyyy] 

这对我有效。

- kam0200