5

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.

Deepak
  • 81
  • 1
  • 1
  • 10

3 Answers3

3

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

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

More about OPENQUERY ...

cableload
  • 4,215
  • 5
  • 36
  • 62
  • Sorry, I forgot to mention but I need to pass arguments in where clause ... but OPENQUERY does not support arguments. – Deepak Jun 07 '16 at 08:53
3

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.

Deepak
  • 81
  • 1
  • 1
  • 10
0

I have found a solution posted by this blogger. Try it out!

This tool from Sysinternals/Mark Russinovich is the best, and my only regret that day was not launching it earlier instead of scouring Google and going insane. I’ve limited Procmon to just sqlservr.exe, as it’s the SQL Service itself that loads/handles the providers and not the ssms.exe. Also of note is that the sqlservr.exe is a 64bit process while the management studio is still just 32bit. As the server service is loading the provider, and the service process is 64bit, the provider must also be available in 64 bit format.

The ODAC112021Xcopy_x64.zip was installed to C:\Oracle. What Procmon showed me however is that sqlservr is attempting to find the oci.dll in any folder but his! (It iterates through the %Path% sysvariable). When it finally gives up on find the dll, the SQL Service is in a unstable shape and the only way to stop the service was to kill it via taskmgr/procexp. Clearly I can see that the “xcopy” deployment – while not giving me any error messages – it also did not set the PATH variable! And this is what this post is really about… adding C:\Oracle and C:\Oracle\Bin to the Path variable or maybe it’s about employing investigative tools earlier in the process instead of relying on your search engine skills.

sqlservr.exe can now find the relevant DLL’s. The OCI.DLL in the root and the OraOLEDB11.DLL in the Bin subfolder. At this point I could query the database! If you did my steps as above and you still get the same error, I strongly suggest using Procmon.exe as I have instead of jumping to the next search result.

Full post is here with more details.

Community
  • 1
  • 1
Qindi Zhang
  • 13
  • 3