problem making Oracle linked server work

[Ellen K]

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

[Dan Guzman]

Re: problem making Oracle linked server work

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" <ekaye2002@yaho o.com> wrote in message
news:1110695346 .557634.70280@l 41g2000cwc.goog legroups.com...[color=blue]
> 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
>[/color]

[Ellen K]

Re: problem making Oracle linked server work

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.

[daleap@hotmail.com]

Re: problem making Oracle linked server work

Did 2.8 solve the problem?

[Ellen K]

Re: problem making Oracle linked server work

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>

[John Bell]

Re: problem making Oracle linked server work

Hi

Did you check out

http://support.microsoft.com/default.aspx?scid=kb;en-us;251238

http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

John

"Ellen K" <ekaye2002@yaho o.com> wrote in message
news:1112595163 .198646.201490@ g14g2000cwa.goo glegroups.com.. .[color=blue]
>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>
>[/color]

[Ellen K]

Re: problem making Oracle linked server work

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 :)

[John Bell]

Re: problem making Oracle linked server work

Hi Ellen

Ellen K wrote:[color=blue]
> Hi John,
>
> Thank you very much!
>
> The second one has a bunch of links to other ones, at one of which I[/color]
at[color=blue]
> 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[/color]
represented[color=blue]
> 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[/color]
of[color=blue]
> 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[/color]
words.[color=blue]
> It's also interesting that even though these articles claim to be[/color]
about[color=blue]
> SQL Server 7, I am having the problem on 2000.[/color]
I think you will have the same behaviour as this is (probably) more to
do with MDAC versions than SQL Server.[color=blue]
>
> One of the workarounds they mention, specifying precision and scale[/color]
of[color=blue]
> any NUMBER columns, we already thought of and tried and it works...[/color]
but[color=blue]
> 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[/color]
if[color=blue]
> 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[/color]
SQL[color=blue]
> 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...[/color]
the[color=blue]
> 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[/color]
a[color=blue]
> process flag column, which I can then read to make sure it worked.[/color]
So[color=blue]
> for the private table if he defines the precision and scale we have[/color]
no[color=blue]
> problem.[/color]

If your Oracle server is male then it will never work!!
[color=blue]
>
> 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[/color]
warehouse[color=blue]
> ETL that need Oracle data, because as previously noted the problem[/color]
does[color=blue]
> 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[/color]
that[color=blue]
> 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.)[/color]

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.
[color=blue]
>
> Meanwhile the part I REALLY don't get is how come *I* didn't find[/color]
these[color=blue]
> articles when I searched on the Microsoft site!!!
>[/color]
C'est la vie.
[color=blue]
> Anyway, thanks again,
>
> Ellen :)[/color]


John

[Ellen K]

Re: problem making Oracle linked server work

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 :)

[DA Morgan]

Re: problem making Oracle linked server work

Ellen K wrote:
[color=blue]
> 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 :)[/color]

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
damorgan@x.wash ington.edu
(replace 'x' with 'u' to respond)

[Ellen K]

Re: problem making Oracle linked server work

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.

[Ellen K]

Re: problem making Oracle linked server work

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