连接到 oracle dbms 时 sql server 上的链接服务器问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4524096/
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
linked server problem at sql server while connecting to oracle dbms
提问by kayhan yüksel
I have created a linked-server definition according to the article at :
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
我根据以下文章创建了链接服务器定义：http:
//www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
My aim is to transfer rows to tables at Oracle 11gR2.
我的目标是将行传输到 Oracle 11gR2 中的表。
After creating linked server, whenever I try to select a table using a query like :
创建链接服务器后，每当我尝试使用以下查询选择表时：
SELECT *
FROM [192.168.1.188]..[ESIPARIS].[T_ERROR_LOG]
I get the error below :
我收到以下错误：
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "192.168.1.188"
supplied inconsistent metadata for a column. The column "EVENT_OBJECT"
(compile-time ordinal 2) of object ""ESIPARIS"."T_ERROR_LOG"" was reported
to have a "LENGTH" of 50 at compile time and 100 at run time.
One more thing is that it duplicates field names whenever a select statment is prepared by "Sql Server Management Studio", some fields are duplicated as below :
另一件事是，每当“Sql Server Management Studio”准备选择语句时，它都会复制字段名称，某些字段会复制如下：
SELECT [EVENT_DATE]
,[EVENT_DATE]
,[EVENT_DATE]
,[EVENT_DATE]
,[EVENT_OBJECT]
,[EVENT_OBJECT]
,[EVENT_OBJECT]
,[EVENT_OBJECT]
,[MESSAGE]
,[MESSAGE]
,[MESSAGE]
,[MESSAGE]
,[EVENT_ID]
FROM [192.168.1.188]..[ESIPARIS].[T_ERROR_LOG]
I would be very happy to hear from you about any ideas, thank you for your concern,
我很高兴听到您的任何想法，谢谢您的关心，
Best Regards,
此致，
Kayhan YüKSEL
凯汉·尤克塞尔
回答by APC
There are a number of scenarios which might throw this error:
有多种情况可能会引发此错误：
- your distributed query in SQL Server references a view with an underlying table in Oracle with a primary key column created in a certain way, Find out more
- there's a bug when the querying a view with numeric columns. Find out more
- it may be a problem with driver incompatibility, such as using the MS OleDB driver instead of the one Oracle provides.
- 您在 SQL Server 中的分布式查询引用了一个视图，该视图具有 Oracle 中的基础表，其中主键列以某种方式创建，了解更多
- 查询带有数字列的视图时出现错误。了解更多
- 这可能是驱动程序不兼容的问题，例如使用 MS OleDB 驱动程序而不是 Oracle 提供的驱动程序。
If it isn't the driver one possible workaround is to use OPENQUERY. Otherwise. this support notecontains general information on troubleshooting linked server and Oracle.
如果它不是驱动程序，一种可能的解决方法是使用OPENQUERY。除此以外。此支持说明包含有关对链接服务器和 Oracle 进行故障排除的一般信息。
(This problem is a fairly generic one, so it turned out that the actual resolution was none of the things I suggested. I'm incorporating @kayhanyüksel's solution in the body of this response for the sake of completeness.)
（这个问题是一个相当普遍的问题，所以结果证明实际解决方案不是我建议的任何事情。为了完整起见，我将@kayhanyüksel 的解决方案合并到这个回复的正文中。）
Solved it with changes at listener and tnsnames. We are now able to connect from SQL Server to Oracle 11gR2 (running on 64 bit Red Hat Enterprise Linux 5.4 ) and vice versa. Documents followed are
- Making a Connection from Oracle to SQL Server
- The Oracle Gateways documentation
通过更改侦听器和 tnsnames 解决了该问题。我们现在可以从 SQL Server 连接到 Oracle 11gR2（在 64 位 Red Hat Enterprise Linux 5.4 上运行），反之亦然。遵循的文档是 -建立从 Oracle 到 SQL Server 的连接
- Oracle 网关文档
回答by Alin Alexandru
I had the same problem: The column ...... was reported to have a "LENGTH" of 50 at compile time and 100 at run time. and duplicate column names when selected.while i was trying to run a query in MS SQL from an ORACLE 11g database
我遇到了同样的问题：据报道，列 ...... 在编译时的“长度”为 50，在运行时为 100。并在选择时重复列名。当我试图从 ORACLE 11g 数据库在 MS SQL 中运行查询时
I used the follownig type of query and it worked !
我使用了 follownig 类型的查询，它奏效了！
DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM TableName'')'
EXEC (@TSQL)
where MyLinkedServer is the name of the linked server and TableName is the name of the table.
其中 MyLinkedServer 是链接服务器的名称，TableName 是表的名称。
here you have the link to the article that helped me: http://support.microsoft.com/kb/314520
这里有帮助我的文章的链接：http: //support.microsoft.com/kb/314520
回答by dim-d
Old thread but it may be useful to someone. When I recently encountered this error, using as provider the MS OleDB driver instead of the Oracle OleDB provider solved the problem.
旧线程，但它可能对某人有用。当我最近遇到这个错误时，使用 MS OleDB 驱动程序而不是 Oracle OleDB 提供程序作为提供程序解决了这个问题。
回答by LucaB
I have the same issue with 11g client but it was disappeared with client version 12 which works for me is using OPENQUERYand to_charwith the field that makes problem.
我在 11g 客户端上遇到了同样的问题，但它在客户端版本 12 中消失了，该版本对我有用OPENQUERY，to_char并且在出现问题的字段中消失了。
I confirm that SQL management studio(no matter what version) gives many duplicated field. The only installing of last driver version we can have consistent queries. I hope it can be useful for you!
我确认SQL management studio（无论什么版本）提供了许多重复的字段。唯一安装最后一个驱动程序版本我们可以有一致的查询。我希望它对你有用！
