Article ID: 221552 - View products that this article applies to.
This article was previously published under Q221552
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:
The query may fail also if the Numeric column is not in the select list.
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 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.
Here are four methods to try to work around the problem:
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,
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog' SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Steps to Reproduce the Behavior
(http://support.microsoft.com/kb/220915/EN-US/ )PRB: SQL Distributed Query with Oracle Needs Oracle Client
Article ID: 221552 - Last Review: December 5, 2003 - Revision: 3.1