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?

4
  • What is the definition of HOST_INTERFACE in Oracle? What version of Oracle's OleDB provider are you using? – David Browne - Microsoft Mar 21 at 15:43
  • I am using Oracle 19c windows client - freshly downloaded. – sherveyj Mar 22 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 – sherveyj Mar 22 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) – sherveyj Mar 22 at 0:40
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.