从SQL Server查询Oracle数据库 [英] Querying an Oracle database from SQL Server

问题描述

我有一个要转移到SQL Server Express 2005中的Oracle 11g XE数据库.

起初，我以为我只是在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')

但是出现错误:

消息7399，第16级，状态1，第1行
OLE DB提供程序"MSDASQL"用于 链接服务器"OracleLinkServer"报告错误.提供者做了 不提供有关该错误的任何信息.
消息7303，第16级，状态1，第1行
无法初始化OLE DB提供程序的数据源对象 链接服务器"OracleLinkServer"的"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

---

按钮:

&是

&否

请帮助！

谢谢

解决方案

如果成功添加了链接服务器，则不再需要OPENQUERY.您可以仅将链接服务器名称作为合格名称的第一部分，如下所示:

SELECT * FROM OracleLinkServer.database.schema.table

不确定需要哪些部分，但点是关键.先尝试一下:

SELECT * FROM OracleLinkServer...oracleTable

I have an Oracle 11g XE database that I would like to transfer into SQL Server Express 2005.

At first I thought I'd just generate the tables in Oracle as SQL, manipulate the data formats, and run the query in SQL Server. This worked for small tables, but I have several tables with a few hundred thousands rows and some with millions of rows, so this solution won't work.

I then created a TNS file with the following content:

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

I followed instructions I found elsewhere on how to generate the ODBC connection, and the 'test connection' was successful.

I then ran these commands to create a Linked Server in 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'

Now I'm trying to query a table in the Oracle database from SQL Server using openquery:

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

But get an error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "OracleLinkServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "OracleLinkServer".

When I check the properties of the Linked Server, and just click the OK, I get this error:

TITLE: Microsoft SQL Server Management Studio Express

"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"

---

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

---

The OLE DB provider "MSDASQL" for linked server "OracleLinkServer" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "OracleLinkServer". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

---

BUTTONS:

&Yes

&No

Please help!

Thanks

解决方案

If you have successfully added your linked server, you no longer need OPENQUERY. You can just include the linked server name as the first part of the qualified name like so:

SELECT * FROM OracleLinkServer.database.schema.table

Not sure which parts you need, but the dots are key. Try this first:

SELECT * FROM OracleLinkServer...oracleTable

这篇关于从SQL Server查询Oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！