SQL Server Link to Oracle Inconsistent metadata problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Server Link to Oracle Inconsistent...

SQL Server Link to Oracle Inconsistent metadata problem

Rate Topic

Display Mode

Topic Options

Author

Message

kats99

Posted Monday, December 03, 2012 8:35 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 10, Visits: 55

Hi
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

Many Thanks

Post #1391984

kats99

Posted Tuesday, December 04, 2012 4:07 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 10, Visits: 55

I am looking at the collations on the linked server to see if that helps

Here are my configurations:
Oracle 11g
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CHARACTERSET = WE8ISO8859P15
NLS_NCHAR_CHARACTERSET = AL16UTF16

SQL 2008 R2 RTM x64
Linked Server Provider - OraOLEDB.Oracle
Linked Server settings - Collation Compatible = False, Use Remote Collation = True, Collation Name = SQL_Latin1_General_CP1_CI_AS

I have also tried with Collation Compatible = True and no Collation Name

Any help would be great
Thanks

Post #1392347