oracle 无法从链接服务器“LinkedServerName”的 OLE DB 提供程序“ProviderName”获取行的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4784966/
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
Cannot get the data of the row from the OLE DB provider 'ProviderName' for linked server 'LinkedServerName'
提问by Ashar Syed
I am running into this issue when I try to execute this query involving Linked Server to Oracle. Find the query underneath,
当我尝试执行这个涉及到 Oracle 的链接服务器的查询时，我遇到了这个问题。找到下面的查询，
SELECT DISTINCT Convert(nvarchar(100),A.ZIP_CD)
,Convert(nvarchar(100),A.CITY)
,Convert(nvarchar(100),A.ST_CD)
,Convert(nvarchar(100),E.PCT)
,Convert(nvarchar(100),B.DEFAULT_DEL_CHG)
,Convert(nvarchar(100),E.DEL_TAX)
FROM [LIVE]..[CUSTOM].MASTER_ZIP A,
[LIVE]..[MISC].ZONE B,
[LIVE]..[MISC].ZIP2ZONE C,
[LIVE]..[MISC].ZIP2TAX D,
[LIVE]..[SALES].TAT E
WHERE A.ZIP_CD = C.ZIP_CD
AND ISNULL(B.DEFAULT_DEL_CHG,0) <> 0
AND A.USPS_PRIM = 'P'
AND C.ZONE_CD = B.ZONE_CD
AND A.ZIP_CD = D.ZIP_CD
--AND decode(D.TAX_CD,'999','99',d.tax_cd) = E.TAT_CD
AND (Case When D.TAX_CD = '999' Then '99' Else D.TAX_CD End) = E.TAT_CD
Here is what I get as an error,
这是我得到的错误信息，
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "LIVE".
无法从链接服务器“LIVE”的 OLE DB 提供程序“OraOLEDB.Oracle”获取行的数据。
I found on a forum that said to try to convert to nvarchar as Oracle has problem converting numerics in sql server, but I still get the same error.
我在一个论坛上发现说要尝试转换为 nvarchar，因为 Oracle 在 sql server 中转换数字时遇到问题，但我仍然遇到相同的错误。
Any idea on how to resolve this issue? Thanks.
关于如何解决这个问题的任何想法？谢谢。
回答by Peter De Winter
For what i' ve found until now, there are a few things that could trigger this error.
到目前为止，我发现有一些事情可能会触发此错误。
- More as 100 results returned. Some people get their queries working when adding "top 100' as a max resultsize.
- Some claim that you should use openquery() with a fully qualified path to table(inc. server) to perform a query on the linked database.
- Other say it is the date format being passed which is not compatible.
- 返回了多达 100 个结果。有些人在添加“前 100 个”作为最大结果大小时使他们的查询有效。
- 有些人声称您应该使用 openquery() 和表（包括服务器）的完全限定路径来对链接的数据库执行查询。
- 其他人说这是传递的日期格式不兼容。
Go figure which one, I haven' t found the solution yet, but maybe one of these 3 will help you.
找出哪一个，我还没有找到解决方案，但也许这三个中的一个会帮助你。
回答by Richard Tovsig
Had this problem on view containing join between sqlserver table and view containing openquery on Oracle.
在包含 sqlserver 表和包含 Oracle 上的 openquery 的视图之间的连接的视图上有这个问题。
select *
FROM vwFMC01
WHERE LOCATIONNAME = '1001002' works ok, but
select *
FROM vwFMC01
WHERE LOCATIONNAME = '1001003' fails!
Join is on two varchar(255) fields. Solved the problem converting the joined fields to char(50):
联接位于两个 varchar(255) 字段上。解决了将joined fields转换为char(50)的问题：
select E.EMPLOYEENUMBER,
E.INITIALS,
E.NAME1,
E.LOCATIONNAME,
L.DESCRIPTION,
from biz_tblEmployee E
inner join vwGerpLocation L
on convert(char(50), E.LOCATIONNAME) = convert(char(50), L.LOCATIONNAME)
回答by ErikE
For one thing you need to give column aliases to those columns.
一方面，您需要为这些列提供列别名。
What happens when you do SELECT DISTINCT 'abc' ABC ...?
当你这样做时会发生什么SELECT DISTINCT 'abc' ABC ...？
回答by KirstieBallance
We had a similar error .
我们有一个类似的错误。
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "LINKEDSERVER". [SQLSTATE 42000] (Error 7346) OLE DB provider "OraOLEDB.Oracle" for linked server "LINKEDSERVER" returned message "ORA-01403: no data found"
无法从链接服务器“LINKEDSERVER”的 OLE DB 提供程序“OraOLEDB.Oracle”获取行的数据。[SQLSTATE 42000]（错误 7346）链接服务器“LINKEDSERVER”的 OLE DB 提供程序“OraOLEDB.Oracle”返回消息“ORA-01403：未找到数据”
and it turns out that the SQL Server database comparability was changed from 2008 to 2014. This ended up corrupting some indexes. We rolled back the change and retried the queries with no issues.
事实证明，SQL Server 数据库的可比性从 2008 年到 2014 年发生了变化。这最终损坏了一些索引。我们回滚了更改并重试了查询，没有出现任何问题。
Since the 2014 compatibility mode was important to us, we changed it to 2014 yet again, rebuilt the indexes and that seemed to eliminate the issues. Hopefully this helps somebody else out there in the w.w.w.
由于 2014 兼容模式对我们很重要，我们再次将其更改为 2014，重建索引，这似乎消除了问题。希望这可以帮助其他人在 www
回答by Gabriel G
Change
改变
... AND (Case When D.TAX_CD = '999' Then '99' Else D.TAX_CD End) = E.TAT_CD
To
到
... AND (Case When D.TAX_CD = '999' Then '99' Else D.TAX_CD End) Like E.TAT_CD
