Post reply

SQL Server Link to Oracle Inconsistent metadata problem

  • December 3, 2012 at 8:35 am

    #285714

    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

  • December 4, 2012 at 4:07 am

    #1565205

    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

  • December 4, 2012 at 4:15 am

    #1565209

    Have you checked this link?

    http://support.microsoft.com/kb/251238

  • December 4, 2012 at 4:18 am

    #1565212

    Ys I did thankyou but it does not apply

  • May 21, 2013 at 2:05 pm

    #1617182

    Did you ever resolve this issue with the linked server to Oracle?

  • May 21, 2013 at 2:23 pm

    #1617187

    I am afraid not. I had to use the open query workaround. 🙁

  • September 5, 2013 at 6:17 am

    #1647673

    Hi,

    i've a similar proble. I've some procedures that uses a oracle linked server, but:

    - if i run manually procedures no errors occur

    - if i run them in a scheduled process it breaks for inconsistent metadata

    - i've no idea which tables/views are the problem source, how can i investigate on it?

    - i've tried to use Profiler without success

    thanks

  • September 5, 2013 at 1:42 pm

    #1647834

    I ended up creating a view of the Oracle table using the TO_CHAR function on the problem columns that were defined as VARCHAR2. I then just use a linked server to Oracle. You could also use the TO_CHAR function directly in your query if you did not want to create the view.

    SELECT * FROM OPENQUERY(LinkedServer,'SELECT to_char(gbaid)

    FROM PRODDTA.F0902

    WHERE gbfy = 99

    ')

    Regards.

  • September 6, 2013 at 5:19 am

    #1648011

    I don't know where is located the problem (table/view and column) because the message of the agent don't explain that.

  • February 23, 2019 at 2:24 am

    #2022734

    michaelkinlb - Thursday, September 5, 2013 1:41 PM

    I ended up creating a view of the Oracle table using the TO_CHAR function on the problem columns that were defined as VARCHAR2. I then just use a linked server to Oracle. You could also use the TO_CHAR function directly in your query if you did not want to create the view. SELECT * FROM OPENQUERY(LinkedServer,'SELECT to_char(gbaid) FROM PRODDTA.F0902 WHERE gbfy = 99 ')Regards.

    Thanks a lot for this solution. Saved my time and your solution worked perfectly.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply