question

Ashwan1234 avatar image
0 Votes"
Ashwan1234 asked ManuelCruz-8834 commented

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

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


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ashwan1234 avatar image
0 Votes"
Ashwan1234 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NicoCF avatar image
0 Votes"
NicoCF answered ManuelCruz-8834 commented

Yo logre que funcionar, efectivamente hay algún cambio en el driver de oracle.


Select * from LINKED..SH.TABLE , no esta soportado. MISMO ERROR.

lo que me FUNCIONO es:

select * from OPENQUERY(LINKED, 'SELECT * FROM SH.TABLE').

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Me sucede lo mismo, eh modificado opciones del proveedor [OraOLEDB.Oracle] al crear el linked server sin éxito alguno, haz intentado actualizar el driver?

0 Votes 0 ·