Linking to an Oracle9i database from SQL Server after upgrade


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,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
agcsupportAuthor Commented:
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.

This link discusses this error as 'message 8':;en-us;280106
It recommends looking at:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agcsupportAuthor Commented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.