Hi all,
I set up our Oracle Financials as a linked server to one of my SQL
Server boxes. On running a test query, I got the following error
message:
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 'TEST_NUM'
(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reported
to have a
DBTYPE of 130 at compile time and 5 at run time].
The Oracle datatype of the column with the supposedly inconsistent
metadata was NUMBER, which according to the Oracle OLE DB documentation
actually maps to 139. 130 is a null-terminated unicode character
string, 5 is a float, and 139 is a variable-length, exact numeric value
with a signed scale value. Oracle NUMBER is an all-purpose numeric
type, apparently they use that instead of int, float, etc. The Oracle
guy used it for this column in the test table because in OF it is used
in pretty much every table. (For starters it is the datatype of their
identity columns.)
There is something in the OLE DB spec about all datatypes having to be
able to be expressed as DBTYPE_WSTR (130), but what I don't get is that
I can connect to the same Oracle instance using VB6 code and the
MSDAORA provider and there is no problem at all interpreting the NUMBER
columns. So why does it work from VB and not as a linked server? And
much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?
TIA 11 13233
You might try upgrading the latest MDAC version (2.8) if you haven't already
done so. You can also run the MDAC component checker to ensure the binaries
match for the installed version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellen K" <ek*******@yahoo.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com... Hi all,
I set up our Oracle Financials as a linked server to one of my SQL Server boxes. On running a test query, I got the following error message: 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 'TEST_NUM' (compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reported to have a DBTYPE of 130 at compile time and 5 at run time].
The Oracle datatype of the column with the supposedly inconsistent metadata was NUMBER, which according to the Oracle OLE DB documentation actually maps to 139. 130 is a null-terminated unicode character string, 5 is a float, and 139 is a variable-length, exact numeric value with a signed scale value. Oracle NUMBER is an all-purpose numeric type, apparently they use that instead of int, float, etc. The Oracle guy used it for this column in the test table because in OF it is used in pretty much every table. (For starters it is the datatype of their identity columns.)
There is something in the OLE DB spec about all datatypes having to be able to be expressed as DBTYPE_WSTR (130), but what I don't get is that I can connect to the same Oracle instance using VB6 code and the MSDAORA provider and there is no problem at all interpreting the NUMBER columns. So why does it work from VB and not as a linked server? And much more importantly, HOW DO I MAKE THE LINKED SERVER WORK?
TIA
Thanks, Dan.
My desktop and the SQL Server box are currently both running the exact
same version of MDAC 2.7 but I guess moving to 2.8 can't hurt.
Did 2.8 solve the problem?
I didn't try it. Honestly I don't see how the problem can be with
MDAC.
As previously noted, using the MSDAORA provider from VB6 I have no
problem talking to Oracle, the problem is only using the MSDAORA
provider to talk to Oracle as a linked server from SQL Server. The
MDAC on the SQL Server box is exactly the same as the MDAC on the box
using VB6.
<shrug>
Hi
Did you check out http://support.microsoft.com/default...b;en-us;251238 http://support.microsoft.com/default...b;en-us;280106
John
"Ellen K" <ek*******@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... I didn't try it. Honestly I don't see how the problem can be with MDAC.
As previously noted, using the MSDAORA provider from VB6 I have no problem talking to Oracle, the problem is only using the MSDAORA provider to talk to Oracle as a linked server from SQL Server. The MDAC on the SQL Server box is exactly the same as the MDAC on the box using VB6.
<shrug>
Hi John,
Thank you very much!
The second one has a bunch of links to other ones, at one of which I at
least found a more exact explanation: "The column with Numeric
datatype has no Length specified (no Precision, no Default, allows
NULL). The number datatype without a precision and scale is represented
in Oracle by a variable-length numeric with precision of up to 255.
There is no SQL Server type that this can be mapped to without loss of
precision." And one of the others states "An Oracle numeric type is
now mapped to nvarchar (384) if the precision is too large for a
numeric SQL Server type."
So -- ta-dah! -- this is how the datatype is being converted at
runtime, although none of the articles explains this in so many words.
It's also interesting that even though these articles claim to be about
SQL Server 7, I am having the problem on 2000.
One of the workarounds they mention, specifying precision and scale of
any NUMBER columns, we already thought of and tried and it works... but
I don't think we can go do that to every NUMBER column in Oracle
Financials, it might not be possible to change them at all and even if
so I don't think it would be a very good idea.
The main reason I wanted the linked server was to be able to make a
distributed transaction to set up products in Oracle Inventory, my SQL
Server transactional database, and my SQL Server data warehouse to
ensure that everybody will be in sync. To do the Oracle piece we
created a private table on the Oracle box, to which I will write... the
Oracle guy has a trigger on it that sends the data to the Oracle
product setup process... when it finishes he comes back and writes to a
process flag column, which I can then read to make sure it worked. So
for the private table if he defines the precision and scale we have no
problem.
It would have been nice to be able to read directly from Oracle also
but I can live without that, just code the parts of the data warehouse
ETL that need Oracle data, because as previously noted the problem does
not occur from VB code.
One of the articles did also mention that MSDAORA is in maintenance
mode and not updated for Oracle versions greater than 8i (we are on
9i), but there is now a .Net managed provider... maybe I will try that
when we go to .Net later this year. (Oracle also puts out their own
OLE DB provider, but with that one I can't even connect.)
Meanwhile the part I REALLY don't get is how come *I* didn't find these
articles when I searched on the Microsoft site!!!
Anyway, thanks again,
Ellen :)
Hi Ellen
Ellen K wrote: Hi John,
Thank you very much!
The second one has a bunch of links to other ones, at one of which I
at least found a more exact explanation: "The column with Numeric datatype has no Length specified (no Precision, no Default, allows NULL). The number datatype without a precision and scale is
represented in Oracle by a variable-length numeric with precision of up to 255. There is no SQL Server type that this can be mapped to without loss
of precision." And one of the others states "An Oracle numeric type is now mapped to nvarchar (384) if the precision is too large for a numeric SQL Server type."
So -- ta-dah! -- this is how the datatype is being converted at runtime, although none of the articles explains this in so many
words. It's also interesting that even though these articles claim to be
about SQL Server 7, I am having the problem on 2000.
I think you will have the same behaviour as this is (probably) more to
do with MDAC versions than SQL Server. One of the workarounds they mention, specifying precision and scale
of any NUMBER columns, we already thought of and tried and it works...
but I don't think we can go do that to every NUMBER column in Oracle Financials, it might not be possible to change them at all and even
if so I don't think it would be a very good idea.
The main reason I wanted the linked server was to be able to make a distributed transaction to set up products in Oracle Inventory, my
SQL Server transactional database, and my SQL Server data warehouse to ensure that everybody will be in sync. To do the Oracle piece we created a private table on the Oracle box, to which I will write...
the Oracle guy has a trigger on it that sends the data to the Oracle product setup process... when it finishes he comes back and writes to
a process flag column, which I can then read to make sure it worked.
So for the private table if he defines the precision and scale we have
no problem.
If your Oracle server is male then it will never work!! It would have been nice to be able to read directly from Oracle also but I can live without that, just code the parts of the data
warehouse ETL that need Oracle data, because as previously noted the problem
does not occur from VB code.
One of the articles did also mention that MSDAORA is in maintenance mode and not updated for Oracle versions greater than 8i (we are on 9i), but there is now a .Net managed provider... maybe I will try
that when we go to .Net later this year. (Oracle also puts out their own OLE DB provider, but with that one I can't even connect.)
At a guess something to do with SQL*Net or the configuration files, I
have never used the OLEDB driver but it may be worth investigating. Meanwhile the part I REALLY don't get is how come *I* didn't find
these articles when I searched on the Microsoft site!!!
C'est la vie.
Anyway, thanks again,
Ellen :)
John
Hi John,
If MDAC was the issue, I would not be able to pull NUMBER data with VB
code. SQL Server is the issue, it's because as the one article noted
it doesn't have any datatype with a precision of 255. (I guess maybe
some scientific applications might need precision of 255, but if I were
designing an RDBMS I would make that some special datatype, I wouldn't
default all numerics to such a thing. It's very arrogant.)
I'm going to try using OPENQUERY instead of the four-part identifier,
with TO_CHAR on any NUMBER data elements that don't have a reasonable
precision specified... I can convert them back to the appropriate
numeric types. If this works I will forget about the Oracle brand
provider, since our Oracle guy already has plenty of work to keep him
busy.
And I'm SURE the Oracle server is male! <ggg>
Thanks again for your help,
Ellen :)
Ellen K wrote: Hi John,
If MDAC was the issue, I would not be able to pull NUMBER data with VB code. SQL Server is the issue, it's because as the one article noted it doesn't have any datatype with a precision of 255. (I guess maybe some scientific applications might need precision of 255, but if I were designing an RDBMS I would make that some special datatype, I wouldn't default all numerics to such a thing. It's very arrogant.)
I'm going to try using OPENQUERY instead of the four-part identifier, with TO_CHAR on any NUMBER data elements that don't have a reasonable precision specified... I can convert them back to the appropriate numeric types. If this works I will forget about the Oracle brand provider, since our Oracle guy already has plenty of work to keep him busy.
And I'm SURE the Oracle server is male! <ggg>
Thanks again for your help,
Ellen :)
Why don't you either (A) read the Oracle documentation which is all
readily available at http://tahiti.oracle.com or (B) post your inquiry
at comp.databases.oracle.server?
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace 'x' with 'u' to respond)
The Oracle documentation CD was the FIRST place I looked. I did find
the definition of the NUMBER datatype there, but nothing else useful.
If you found something specific that would be helpful, please post it.
All,
The solution turned out to be to use OPENQUERY instead of the four-part
qualifier.
I am a very happy camper right now. :)
Thanks to all who helped,
Ellen This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: David Gray |
last post by:
Hello all,
Having problems connecting to an Oracle 9i database from within
SQL/Server 2000 using the Security/Linked Servers feature.
Server1 (SQL/Server)
-----------
Windows Server 2003,...
|
by: Pardhasaradhy |
last post by:
Hello,
I have a linked server to oracle 7.1 from SQL 2000. when I try to
execute simple select statement which returns one row of data using
openquery is not fetching the data. After 30 minutes...
|
by: Raja |
last post by:
I am transferring data from SQl Server 2000 to Oracle through Linked
Servers.It takes considerable amount of time while transferring data
from SQL Server to oracle.Both these databases are at...
|
by: robin via SQLMonster.com |
last post by:
I've tried several different way to execute a oracle stored
procedure from a DTS package but to no avail.
I have a Linked Server setup which does bring back Oracle tables from the
server when I...
|
by: tim.pascoe |
last post by:
I'm currently trying to establish a linked server to an Oracle
database.
Setup:
Connecting to 8x version of Oracle
Using 9i client tools (Net Manager)
SQL-Server 2000
Windows 2000
I...
|
by: Alfonso Esteban Gonzalez Sencion |
last post by:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.
When I consult the linked...
|
by: Crazy Cat |
last post by:
Hi,
I created a linked server for MS SQL Server 2005 Express to an Oracle
database using the OLE DB Provider for ODBC. My ODBC Source uses the
Microsoft ODBC for Oracle driver.
I'm using the...
|
by: jonceramic |
last post by:
Hi All,
I started developing in Access, and people took notice and so we're
starting to migrate into our corporate's bigger Oracle system.
I'll still be using my developed Access front ends,...
|
by: Mark D Powell |
last post by:
I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. The
developer said that he needed to use OLE to talk to Oracle so I went
to Oracle and downloaded the 64 bit OraOLDDB driver as...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |