使用链接服务器从OLE服务器查询Oracle数据库(OLE DB提供程序OraOLEDB.Oracle) [英] Querying Oracle database from SQL server using linked server (OLE DB provider OraOLEDB.Oracle)
问题描述
亲爱的,
我正在尝试从Microsoft Sql Server(2014)查询Oracle数据库(11.2.0.4)上的视图。如果没有openquery,我无法查询
I am trying to query a view on Oracle database (11.2.0.4) from Microsoft Sql Server (2014). I am unable to query it without openquery
以下是示例 -
Here's the example -
select * from link_oracle.ODB.sopuser.sop_details
select * from link_oracle.ODB.sopuser.sop_details
但我可以使用OPENQUERY进行查询。这是查询
But i can query using OPENQUERY. Here's the query
从openquery中选择*(link_oracle,'select * from sopuser.sop_details')
select * from openquery(link_oracle, 'select * from sopuser.sop_details ')
这里的事情是总共有3k行,但当我查询"SELECT * FROM"时我只得到101行,并且出现错误
The thing here is that there are around total 3k rows, but when i query "SELECT * FROM" i get only 101 rows and an error saying
"消息7399,等级16,状态1,行3 <
OLE DB提供商"OraOLEDB.Oracle" ;对于链接服务器"link_oracle"报告错误。提供商没有提供有关错误的任何信息。
消息7330,级别16,状态2,行3
无法从OLE DB提供程序"OraOLEDB"获取行。甲骨文"对于链接服务器"link_oracle"。"
"Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "OraOLEDB.Oracle" for linked server "link_oracle" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "link_oracle"."
但是我能够使用此查询获取行 - select * from openquery(link_oracle,'select * from sopuser.sop_details where rownum< = 150')
But i am able to fetch rows using this query -- select * from openquery(link_oracle, 'select * from sopuser.sop_details where rownum <=150')
任何人都可以帮我这个。
Can anybody help me on this.
推荐答案
从link_oracle中选择*。 ODB .sopuser.sop_details
select * from link_oracle.ODB.sopuser.sop_details
您好,
什么是ODB,数据库名称? Oracle实例是一个数据库,不使用Name;将其从查询中删除=>
What is ODB for, the database Name? A Oracle instance is one database and that's Name isn't used; remove it from the query =>
select * from link_oracle.sopuser.sop_Details
这篇关于使用链接服务器从OLE服务器查询Oracle数据库(OLE DB提供程序OraOLEDB.Oracle)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!