Text 3516, 79 rader
Skriven 2005-04-09 21:50:44 av Ellen K. (1:379/45)
   Kommentar till text 3417 av Ellen K. (1:379/45)
Ärende: Re: problem linking Oracle from SQL Server
==================================================
From: Ellen K. <72322.1016@compuserve.com>

OPENQUERY works perfectly, do not even need to convert the NUMBER data!

What a lot of experimenting and worrying for something that turned out to be
simple to work around!

(Have not figured out how to use OPENQUERY to write, only to read, but for now
that's OK because I'm only writing to private tables we're creating ourselves,
where specifying the precision of the NUMBER columns solves the problem.  We
then put triggers on the private tables that send the data to the OF process we
want to use.)

On Tue, 05 Apr 2005 12:11:45 -0700, Ellen K. <72322.1016@compuserve.com> wrote
in message <cjo551d6r7rch892oivcg35fqg10rj0l6p@4ax.com>:

>In case anyone's interested:
>
>I found some more articles on the Microsoft site that I think filled in
>the missing pieces of the puzzle, although nowhere does MS explain this
>in so many words in one place.
>
>The Oracle NUMBER datatype has a maximum precision of 255.  SQL Server
>does not have any datatype with a precision that high.  (Why one would
>ever be needed is a separate question...  If I were designing an RDBMS
>and wanted to include this to accomodate scientific applications or
>whatever, I would make it a special datatype.  Defaulting all numeric
>data elements to this datatype is really arrogant in my opinion.)
>
>So therefore if a NUMBER column is defined without any precision
>specified, the precision defaults to 255 and it gets converted to
>varchar (384) when SQL Server tries to read it.  This explains the
>"metadata was changed at runtime" error message.  I think it also
>explains why I can read the Oracle data using VB code -- I think VB
>doesn't care about the precision specified for the column, as long as
>what's IN the column fits into an ADO datatype.
>
>I am going to try using an OPENQUERY statement instead of a four-part
>qualifier, and apply TO_CHAR to the NUMBER columns, then convert the
>values back to some appropriate numeric type in SQL Server if necessary.
>I did not previously know about OPENQUERY, apparently it allows querying
>a target RDBMS using the TARGET's syntax, i.e. it's a pass-through.
>Hope it works.
>
>On Sun, 13 Mar 2005 11:22:30 -0800, Ellen K. <72322.1016@compuserve.com>
>wrote in message <mg49315n8nqbi7j3qqvou05g20cr0uvvlc@4ax.com>:
>
>>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?
>>
>>
>>

--- BBBS/NT v4.01 Flag-5
 * Origin: Barktopia BBS Site http://HarborWebs.com:8081 (1:379/45)