I have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can query the Oracle database using OPENQUERY but when I try a simple SELECT COUNT(*) from LK_ORA..USR.TAB1 I get an error:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LK_ORA" supplied inconsistent metadata for a column. The column "COL_CODE" (compile-time ordinal 1) of object ""USR"."TAB1"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.
I've found an article that says when SQL Server retrieves metadata from Oracle in order to compile the query, Oracle reports the data type to be varchar(255), but when it actually returns the data, the data type is in fact nvarchar(255). (But it may not be called nvarchar in Oracle.)
Anyone know how to get around this? I've tried various options on my linked server connection in SQL with no luck so far