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:
Server: Msg 7354, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA'
supplied invalid metadata for column 'columnname'. The data type is not
The query may also fail if the numeric
column is not in the select list.
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:
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:
INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
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:
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.
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.
An Oracle numeric
type is now mapped to nvarchar
(384) if the precision is too large for a numeric
SQL Server type.