Linking to an Oracle9i database from SQL Server after upgrade

Posted on 2006-04-15
Last Modified: 2012-06-27

I just recently updated our Oracle 9i database from to  The upgrade went fine except for the
following problem:

I have a SQL Server 2000 that links to the the Oracle database.  I extract some data from the Oracle to update other
systems on SQL Server.  It has been working for a long time.  After the patch was put in place i'm getting the
following message when I try to run the process that links to Oracle then extracts the data:

-- error message -------------------------
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error:  Column 'GBCTRY' (compile-time ordinal 2) of object '"PRODDTA"."F0902"' was reported to have a DBTYPE of 130 at compile time and 5 at run time].
-- error message -------------------------

The SQL Server(2000 SP4) machine is running Windows 2003 SP1. From what I can tell it has MDAC2.8 and Oracle Clent

Any ideas??  Is this an MDAC issue or Oracle client tools version.

Thank you,

Question by:agcsupport
    LVL 16

    Expert Comment

    I think you need to re-establish the connection from sqlserver to oracle. From the error, it sounds like when the connection for extraction of the data was originally established, the datatype was reported differently that it is with the 9i database.  I think if you can re-establish the routine that connects to oracle, then it should work fine.

    I think the error message is fairly clear "DBTYPE of 130 at compile time and 5 at run time"
    The database internal data type has change with the upgrade and sqlserver needs to be recompiled with the new database.

    Author Comment

    Thanks for your comments RCorfman.

    I have actually dropped the link server and re-create and still got the same message.  I also created a new link server with a different name and no luck.  I also have a separate SQL 2005 installation on another machine where I created a linked server to Oracle and continue tha have the same message I mentioned above.

    Any other ideas greatly appreciated.

    LVL 16

    Expert Comment

    This link discusses this error as 'message 8':;en-us;280106
    It recommends looking at:
    LVL 4

    Accepted Solution

    Following are the Gotchas that I encountered and the solutions that I arrived at to fix them.

    Case Sensitivity

    Having successfully created the linked server with no error messages, I tried to query dbatest_table table in the dbatest_schema:

    select * from dbatest_server..dbatest_schema.dbatest_table

    Which returned the error:

    Server: Msg 7314, Level 16, State 1, Line 1
    OLE DB provider 'dbatest_server ' does not contain table '"dbatest_schema"."dbatest_table"'.
    The table either does not exist or the current user does not have permissions on that table.

    After some more pondering, I remembered that Oracle stores all its table name in upper case. Out of curiosity, we tried to use the ‘upper’ function but that didn’t solve the problem either:

    Server: Msg 155, Level 15, State 1, Line 1
    'dbatest_table' is not a recognized OPTIMIZER LOCK HINTS option.

    However, the following query worked perfectly:

    select *
     from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

    Lesson Number 1: write all Oracle table names in UPPER CASE.

    2. What’s in a Registry?

    Books Online mentioned that the client's registry must be modified by running a registry file from a command line. The file is to be found in: “C:\Program Files\Common Files\System Files\OLE DB”. However, I did not find that this was causing us a problem and I am still using the Registry settings for an Oracle 7 database against an Oracle 9.2 database. The application is not yet running live and so may yet cause us plenty of problems but I have nonetheless managed to establish a connection from SQL Server to Oracle using old Registry keys.

    Oracle Client
     Microsoft Windows 2000
     [HKEY_LOCAL_MACHINE\SOFTWARE [HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\TransactionServer Microsoft\MSDTC\MTxOCI] \Local Computer\My Computer] "OracleXaLib"="xa73.dll" "OracleXaLib"="xa73.dll" "OracleSqlLib"="SQLLib18.dll" "OracleSqlLib"="SQLLib18.dll" "OracleOciLib"="ociw32.dll"

    Lesson Number 2: the Registry settings might be a factor but for us they weren’t.

    3. Not all roads lead to Rome: problems with datatypes

    First, NUMBER, precision & scale…………….. and the Four-Part Query. The DBATEST_TABLE was created on the Oracle database with the following column definitions:

    Name                                        Null?    Type
    ----------------------------------------- -------- ----------------------------
    INTID                                      NOT NULL VARCHAR2(10)
    FILEID                                      NOT NULL CHAR(1)
    PROCID                                        NOT NULL VARCHAR2(10)

    Running the following four-part query……….

    select * from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

    …returned the following error:

    Server: Msg 7356, Level 16, State 1, Line 1
    OLEDB provider 'MSDAORA' supplied inconsistent metadata for a column.
    Metadata information was changed at execution time.

    The Microsoft website referred us to Knowledge Base article 251238 ( Needless to say, following the steps in the article failed to reproduce the error message & the query worked perfectly! Dammit.

    However, further attempts to query the Oracle database revealed that OPENROWSET & OPENQUERY both worked fine against DBATEST_TABLE and returned the correct data.

                     FROM DBATEST_TABLE')
    ('NNNN.NNNN.NNNN.NNN' = the top line from the TNSNAMES.ora file)

    Thus, I was presented with a dilemma: why did OPENROWSET & OPENQUERY work but the four-part query fail? Further investigation revealed the problem to be the NUMBER column in Oracle. If the precision and scale were specified during the table creation, the four-part query works fine. No precision and scale resulted in errors.

    Lesson Number 3: the four-part query can be unreliable. Use OPENQUERY & OPENROWSET in preference.

    Next, looking at NUMBER, precision & scale……. and truncation. In Oracle, NUMBER datatypes are truncated to the right of the decimal point. In SQL Server, the precision & scale are shown in their entirety and there is no truncation. In the following example, the Oracle table was created with the precision & scale being specified (NUMBER 15,10). The Four-Part Query, OPENROWSET & OPENQUERY all returned the same results:

    Oracle results SQL Server results
    4.01  4.0100000000
    4.02  4.0200000000
    4.03  4.0300000000
    4.04  4.0400000000

    Lesson Number 4: be prepared for data to be returned in SQL Server in a slightly different format to Oracle.

    Third, let's look at NUMBER, precision & scale…………. and rounding. In the next example, the Oracle table was created without precision & scale being specified but with decimals being put into the column values. The Four-Part Query failed, as expected, but it was found that OPENROWSET & OPENQUERY rounded the values that they returned in some quite unexpected ways:

    Oracle results SQL Server results
    4.01  4.0099999999999998
    4.02  4.0200000000000005
    4.03  4.0300000000000002
    4.04  4.04
    4.05  4.0499999999999998
    4.06  4.0600000000000005
    4.07  4.0700000000000003
    4.08  4.0800000000000001
    4.09  4.0899999999999999

    However, when precision & scale were specified, the values returned by OPENROWSET & OPENQUERY were correct:

    Oracle results SQL Server results
    4.01  4.0100000000
    4.02  4.0200000000
    4.03  4.0300000000
    4.04  4.0400000000
    4.05  4.0500000000
    4.06  4.0600000000
    4.07  4.0700000000
    4.08  4.0800000000
    4.09  4.0900000000

    Lesson Number 5: always use precision and scale. The four-part query should work and there won’t be any strange rounding in SQL Server.

    Lastly, NUMBER, precision & scale…………. and commits. In the final example, the Oracle table was created with precision & scale, values were entered and a query run against the table to check that the data had been successfully entered; they were all returned successfully. However, the same query when run against SQL Server only returned the column heading and no values.

    Lesson Number 6: always remember to commit in Oracle.

    Chetan Sachdeva

    Author Comment

    I did find a work around that may be suitable for my situation.  Any table that was created in Oracle with the NUMBER datatype but no (precision, Scale) explictly assigned the link server from SQL Server would not work.  It would give me the error mentioned earlier on this email.  If the table created in Oracle with a NUMBER datatype had (precision, scale) I was able to retrieve data with no problem.  It works fine as well with all tables with CHAR columns datatypes.

    In short I just need to make sure the tables I retrieve from need to have (Precision and Scale) in the NUMBER datatypes when created.

    My concern was that it was working fine with Oracle but once I patch it to the behavior changed and could not link from those type of tables with the NUMBER data types and no (Precision and Scale) assigned.

    Maybe there is a fix out there but this work around will work for now.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    This article describes some very basic things about SQL Server filegroups.
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now