如何使用SQL2000链接服务器查询Oracle 11G表 [英] How to use a SQL2000 Linked Server to query an Oracle 11G table

问题描述

有人可以帮助我使用链接服务器"idwd"构造查询Projects_dim表所需的SQL吗?

Can someone help me construct the SQL that I need to query the Projects_dim table using the Linked Server "idwd"?

要测试连接，我使用链接的服务器名称运行了一个示例查询.要访问链接服务器上的表，我使用了一个四部分的命名语法:

To test the connection, I ran a sample query using the linked server name. To access the tables on the linked server, I used a four-part naming syntax:

链接服务器名称.目录名称.模式名称.表名称.

linked_server_name.catalog_ name.schema_name.table_name.

替换值，您将得到:

idwd.idwd.wimr.PROJECTS_DIM

应该是以下哪个?

idwd..wimr.PROJECTS_DIM

数据库名称为"idw"，但下面的网格在"catalog"下显示了空白值，这是我感到困惑的原因之一，尽管我认为，更可能的方法是在假定目录的一部分的情况下构造语法.合格的表名应为空，如下面的第一个示例所示.

The database name is "idw" but the grid below shows a blank value under "catalog", which is one source of my confusion, though I believe that the more likely approach is to construct the syntax assuming that the catalog part of the qualified table name should be blank as in the following first example.

    select * from idwd..wimr.PROJECTS_DIM

    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'idwd' does not contain table '"wimr"."PROJECTS_DIM"'.  The table either does not exist or the current user does not have permissions on that table.

select * from idwd.idwd.wimr.PROJECTS_DIM

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

有人可以建议我查询该表需要做什么吗?

Can someone suggest what I need to do to query this table?

我正在使用MS OLEDB Driver for Oracle.

I am using the MS OLEDB Driver for Oracle.

我认为也许区分大小写有问题，所以我尝试了这个问题:

I thought perhaps there is an issue with case-sensitivity, so I tried this:

select * from IDWD..WIMR.PROJECTS_DIM


Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

和这个:

select * from IDWD.IDWD.WIMR.PROJECTS_DIM

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDAORA'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

我尝试使用两个可能的驱动程序中的每一个来创建链接服务器:

I tried to create a linked server using each of the two likely drivers:

1. Microsoft OLEDB提供商 甲骨文
2. 用于OLEDB的Oracle Provider

1. Microsoft OLEDB Provider for Oracle
2. Oracle Provider for OLEDB

..没有运气.

您认为这可能是驱动程序问题吗?

Do you think it could be a driver issue?

推荐答案

我刚刚解决了此问题.如果甲骨文以前曾做过，那么它可能是最近升级的.

I just resolved this issue. Oracle was probably upgraded recently if it did worked before.

为解决此问题，我连接到Oracle，并做了一个"descr TABLENAME;".并检查是否有问题的列.就我而言，我的列类型为NUMBER，没有像此预览中那样的任何比例.

To resolve I connected to Oracle and did a "descr TABLENAME;" and check whatever column was in problem. In my case, I had a column type as NUMBER without any scale like in this preview.

我要求DBA强制对这3列进行缩放，现在问题就解决了！

I asked a DBA to force a scale for these 3 columns and problem is now solved!

但是我确实发现此解决方案也有解决方法.您还可以更改TSQL

But I did find also there is a workaround this solution. You can also change the TSQL

来自

SELECT * FROM idwd..wimr.PROJECTS_DIM

到

SELECT * FROM OPENQUERY(idwd,'select * from wimr.PROJECTS_DIM')

如果未在列上设置可空性，Microsoft支持部门将报告一个问题.

And Microsoft Support report a problem if nullability is not set on a column.

有关我的 查看全文