SQL Server Oracle linkserver (orale driver 12.2 client 64bit) error

Question

SQl server version is 2016 SP2 enterprise.

Created a link server name CRMLINK.
oracle driver installed :12.2 64bit

When do a sql I am having error

select * from CRMLINK.<schema>.tablename
+++++++++++++++++++++++++++++++++++++

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "CRMLINK" supplied inconsistent metadata for a column. The column "MOD_DATE" (compile-time ordinal 6) of object ""CRMLINK"."crm001"" was reported to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 0 at run time.

+++++++++++++++++++++++++++++++++++++++
But connection is success when we use in this way OPENQUERY

SELECT * from OPENQUERY(CRMLINK,'select * from <schema>.tablename')

+++++++++++++++++++++++++++++
Developers are need to access first way not using openquery.

Any one knows why this is happening
Thanks

Answer 1

I ran into this error with a different provider, but I was able to resolve that error with setting "Allow InProcess" to 1 for the provider. I don't know if this helps with the Oracle provider, but it is something you could try.

You set this in Object Explorer from Server Objects->Linked servers->Proivders.

Answer 2

Hi Ashwan1234,

This issue is more related to Oracle.
You can try to upgrade the Oracle Provider for OLE DB.
Please refer to the following Oracle Support articles which might help:
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2484393_1.html
https://support.oracle.com/knowledge/Oracle%20Database%20Products/1106034_1.html
In addition, you can open a thread in the Oracle forum so that people there will help you more effectively.

Best Regards,
Amelia

--
Please remember to upvote the responses that resolved your issue and Accept it as Answer.

Answer 3

It seems that the Oracle provider returns incorrect metadata about the column. Why it is so, I don't know. Maybe you could try to update the provider in case you have an old version. But it could also be something funky with the column in the Oracle database.

Unfortunately, this is more of an Oracle problem than an SQL Server problem.

Answer 4

Hi Ola
Sorry I missed. yes we use as follows and got the same error
select * from CRMLINK..<schema>.tablename

Answer 5

select * from CRMLINK.<schema>.tablename

Is that really your query? Because accessing a linked server we use 4-part qualifier, you used 3 part, so add an additional dot =>

select * from CRMLINK..<schema>.tablename

Additional see
the-ole-db-provider-quotoraoledboraclequot-for-linked-server-supplied-inconsistent-metadata