MSSQL Linked Server error: The OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column

Question

I was trying to pull data from Oracle to MSSqlserver database using Linked server.

select * from [LINK_NAME]..SCHEMA.TABLE;

But it was failing with the below error:

The OLE DB provider "OraOLEDB.Oracle" for linked server "LINK_NAME" supplied inconsistent metadata for a column. The column "COLUMN_NAME" (compile-time ordinal 6) of object ""SCHEMA"."TABLE"" was reported to have a "LENGTH" of 100 at compile time and 200 at run time.

I also need to pass argument at run time in where condition. I found OPENQUERY as a solution but it does not support arguments at runtime.

Answer 1

Try using the OPENQUERY Syntax to see whether that helps..

SELECT * FROM OPENQUERY(LINK_NAME, 'SELECT * FROM db.Schema.Table')

More about OPENQUERY ...

Answer 2

I found solution:

The error was coming due to database column type mismatch. ORACLE was using NVARCHAR for datatype but in case of SQLSERVER it was VARCHAR.

As NVARCHAR is double the size of VARCHAR that is why it was showing size mismatch error.

Changing the data type to same worked for me.