1

Having issues selected data for some Oracle tables over the linked server from SQL Server:

select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc;

Msg 7356, Level 16, State 1, Line 13 The OLE DB provider "OraOLEDB.Oracle" for linked server "RMTDB" supplied inconsistent metadata for a column. The column "CREATE_TIME" (compile-time ordinal 3) of object ""JOB1308"."HOST_INTERFACE"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

Open query version of it works:

select * from OPENQUERY( RMTDB, 'select type, count(*) from HOST_INTERFACE group by type order by 1 desc')

result

TYPE      COUNT(*)
10          10450
8            6543
6            21

Anyone run into this?

Improve this question
4
  • What is the definition of HOST_INTERFACE in Oracle? What version of Oracle's OleDB provider are you using? Commented Mar 21, 2021 at 15:43
  • I am using Oracle 19c windows client - freshly downloaded. Commented Mar 22, 2021 at 0:32
  • SQL> describe host_interface Name Null? Type ---------------- -------- ------------------------------------------------- MESSAGE_ID NUMBER(9) TYPE NUMBER(8) CREATE_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE READ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE STATUS NUMBER(9) STATUS_DETAILS VARCHAR2(1000) RECEIVER NUMBER(9) PAYLOAD CLOB Commented Mar 22, 2021 at 0:35
  • This view works ok: SQL> describe V_INVENTORYSTACKS; Name Null? Type --------------------- -------- ------- STACK NOT NULL NUMBER(3) ROBOT_ZONE NOT NULL NUMBER(2) SKU NOT NULL NUMBER(12) SKU_DESCRIPTION VARCHAR2(40) STATE VARCHAR2(8) LOCK_BOTTOM_LAYER NOT NULL NUMBER(1) REQUEST NOT NULL NUMBER(7) LAYERS NOT NULL NUMBER(2) LOT VARCHAR2(10) MEASURE NOT NULL NUMBER(1) EMPTY NOT NULL NUMBER(1) Commented Mar 22, 2021 at 0:40

2 Answers 2

Reset to default
0

I found a support.oracle.com Doc ID 1519958.1 that describes the issue.
For solution it says: Use the relatively simple work around of linking to a view that is based upon the table, instead of directly to the table.

Apparently this issue has been around since 2012.

Improve this answer
0

this still seems to be an issue with 2019. In my case I am able to query just the oracle table via openquery but was not able to join from sql server table to the openquery without getting this error. Also, 4-part qualifier syntax (alternative to openquery) gives error as well. Even just querying the oracle table by itself.

A simple solution I found is to rearrange the query putting the openquery first in the from clause and joining from it to the sql server table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.