Can someone help me build the SQL that I need to query the Projects_dim table using Linked Server "idwd"?
To test the connection, I executed a sample request using the name of the linked server. To access tables on a linked server, I used a four-part naming syntax:
linked_server_name.catalog_ name .schema_name.table_name.
replacing the values, you get:
idwd.idwd.wimr.PROJECTS_DIM
of should he be next?
idwd..wimr.PROJECTS_DIM
The database name is "idw", but the table below shows an empty value in the "directory", which is one of the sources of my confusion, although I think the more likely approach is to build a syntax assuming that part of the directory the name of the qualified table should be empty, 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 tell me what I need to do to query this table?
I am using the MS OLEDB driver for Oracle.
I thought there might be a case sensitivity case, 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.
and this:
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 two possible drivers:
- Microsoft OLEDB Provider for Oracle
- Oracle Provider for OLEDB
..without luck.
Do you think this could be a problem with the driver?
