在 SQL Server2008 R2 中从链接服务器 (Oracle) 运行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6370601/
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
Run a Query from Linked Server (Oracle) in SQL Server2008 R2
提问by TTCG
I have the linked server set up in SQL Server 2008. But I could not run any query against the linked server.
我在 SQL Server 2008 中设置了链接服务器。但我无法对链接服务器运行任何查询。
I tried to run this simple command but it's not working
我试图运行这个简单的命令，但它不起作用
SELECT * FROM MYSERVER..ALANH.TEMP_UPDATE1
This is the error I got when I run the above command.
这是我运行上述命令时遇到的错误。
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "MYSERVER" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "MYSERVER". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Could anyone help me to connect to the OracleLinkedServer? Thanks very much.
谁能帮我连接到 OracleLinkedServer？非常感谢。
回答by fasr
you can be that way too:
你也可以这样：
**SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM ALANH.TEMP_UPDATE1')**
回答by Сергей
You can write the query like this:
您可以像这样编写查询：
select * FROM [MYSERVER]..[ALANH].[TEMP_UPDATE1]
Important: In this case, the fully qualified table name must be written in upper case.
重要提示：在这种情况下，完全限定的表名必须大写。
回答by Richard
You might try the fix from this article.
您可以尝试本文中的修复程序。
Also, this could be a problem with naming. From an MS KB article
此外，这可能是命名问题。来自MS KB 文章
If you receive these error messages, a table may be missing in the Oracle schema or you may not have permissions on that table. Verify that the schema name has been typed by using uppercase. The alphabetical case of the table and of the columns should be as specified in the Oracle system tables.
On the Oracle side, a table or a column that is created without double quotation marks is stored in uppercase. If the table or the column is enclosed in double quotation marks, the table or the column is stored as is.
如果您收到这些错误消息，则 Oracle 架构中可能缺少某个表，或者您可能没有对该表的权限。验证是否已使用大写字母键入架构名称。表和列的字母大小写应与 Oracle 系统表中指定的一样。
在 Oracle 端，不带双引号创建的表或列以大写形式存储。如果表或列用双引号括起来，则表或列按原样存储。
回答by Hemanshu Trivedi
First make sure the tnsping utility works from client server, then use the below string in linked server database source setup
首先确保 tnsping 实用程序在客户端服务器上工作，然后在链接服务器数据库源设置中使用以下字符串
host[:port]/service_name
Check this link for more details :
查看此链接了解更多详情：
回答by Caseraw
Try something like this:
尝试这样的事情：
SELECT * FROM ALL_TABLES@"SOME.SERVER.NAME";
In this case I'm selecting ALL_TABLESfrom a linked server called SOME.SERVER.NAME.
在这种情况下，我选择ALL_TABLES从链接服务器调用SOME.SERVER.NAME。
回答by Hyman Hadley
Richard's post above was critical.
理查德在上面的帖子很关键。
I am using v12 ODP.NET odbc drivers and had to ensure that "Zero Level Only" was NOT checked and that the names supplied for table and schema were the correct case. All of the schemas and tables I access are uppercase only.
我正在使用 v12 ODP.NET odbc 驱动程序，并且必须确保未检查“仅零级”并且为表和架构提供的名称是正确的情况。我访问的所有模式和表都是大写的。
Use the query below to determine what the correct table name is, though you will have to supply the schema name in the correct case for the query to work. Try all uppercase, try all lowercase, try mixed case, or better yet get the actual name from the dba (I've heard that only table/schema names that are "" quoted will be allowed mixed case, otherwise in oracle it's all uppercase.)
使用下面的查询来确定正确的表名称是什么，但您必须以正确的大小写提供模式名称才能使查询工作。尝试全部大写，尝试全部小写，尝试混合大小写，或者更好地从 dba 获取实际名称（我听说只有被 "" 引用的表/模式名称才允许混合大小写，否则在 oracle 中全部为大写.)
sp_tables_ex @table_server=InsertLinkedServerHere, @table_schema=InsertSchemaNameHere
sp_tables_ex @table_server= InsertLinkedServerHere，@table_schema= InsertSchemaNameHere
