Article ID: 243027 - View products that this article applies to.
This article was previously published under Q243027
BUG #: 36489 (SQLBUG_70)
BUG #: 18722 (SQLBUG_70)
BUG #: 47131 (SQLBUG_70)
Attempting to use an Oracle linked server using the Microsoft OLE DB Provider for Oracle (MSDAORA) that queries a table with a numeric column may cause an error similar to the following:
The query may also fail 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 the numeric data type has no length specified (no precision, no default, allows NULL). The numeric data type without a precision and scale is represented in Oracle by a variable-length numeric with precision of up to 255.
For more information about possible workarounds, see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/221552/EN-US/ )PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561For more information, contact your primary support provider.
(http://support.microsoft.com/kb/254561/ )INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
Even though SQL Server 7.0 Service Pack 2 (SP2) contains a fix of the root cause of the described problem, the setup of SP2 does not install an updated version of the sp_columns_ex catalog stored procedure. So a call to sp_columns_ex may still return the following error:
To correct this problem, run the INSTCAT.SQL script from SP2. Please see "How to Upgrade the Catalog Stored Procedures" topic in SQL Server 7.0 Books Online for instructions on how to run this script.
Server: Msg 515, Level 16, State 2, Procedure sp_columns_ex, Line 133
Cannot insert the value NULL into column 'DATA_TYPE', table 'tempdb.dbo.#tmp_columns_XX'; column does not allow nulls. INSERT fails. The statement has been terminated.
An Oracle numeric type is now mapped to nvarchar (384) if the precision is too large for a numeric SQL Server type.