Microsoft KB Archive/221552

= PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error =

Article ID: 221552

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q221552



SYMPTOMS
Attempting to use an Oracle linked server using the MSDAORA OLE DB provider, which queries a table with a NUMERIC column, may give an error similar to the following:

Server: Msg 7354, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' supplied invalid metadata for column 'columnname'. The data type is not supported.

The query may fail also if the Numeric column is not in the select list.



CAUSE
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.



WORKAROUND
Here are four methods to try to work around the problem:

 Set the Length property for NUMERIC column in Oracle table. If you are not retrieving the NUMERIC column, one workaround is to use OPENQUERY instead of a four-part name in your distributed query.  Use the TO_CHAR function to convert the data in the numeric field to a character value (note that you may have to convert it back for applications that require math operations). For example:

SELECT * FROM OPENQUERY(mylinkedserver, 'select TO_CHAR(F1) from table1')  The best workaround, if possible, is to change the field type in the table to a defined value, such as NUMERIC(15), because many non-Oracle applications have trouble with this data type. However, many servers have corporate restrictions on what can be changed and who can change them. If you can't change the data type on the server, then use one of the other options above.



MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,

<pre class="fixed_text">sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')

Steps to Reproduce the Behavior
<ol>  Create a table on Oracle server: <pre class="fixed_text">Create table NumTable ( f1 NUMBER )

insert into NumTable values ( 2.3 ); </li>  set up a linked server to Oracle server:

<pre class="fixed_text">EXEC sp_addlinkedserver  'OracleServer',  'Oracle',  'MSDAORA',  'myOracleServer'

go

EXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'

go </li>  Specify a four-part name query:

<pre class="fixed_text">SELECT * FROM OracleServer..DEMO.NUMTABLE which will report an error:

Server: Msg 7354, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' supplied invalid metadata for column 'F1'. The data type is not supported.

</li>  Add another column to the table:

<pre class="fixed_text">Alter table numtable add f2 integer;

Insert into numtable values ( 2.1, 23 ); </li>  Specify a distributed query using OpenQuery:

<pre class="fixed_text">SELECT f2 FROM OPENQUERY(OracleServer, 'SELECT f2 FROM demo.NumTable') </li></ol>

For additional information, please see the following article in the Microsoft Knowledge Base:

220915 PRB: SQL Distributed Query with Oracle Needs Oracle Client

Keywords: kboracle kbprovider kbdatabase kbprb KB221552

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.