FIX: Numeric Column in Oracle Causes Error 7354 (243027)

SYMPTOMS

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

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

CAUSE

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.

WORKAROUND

For more information about possible workarounds, see the following article in the Microsoft Knowledge Base:

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

STATUS

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:

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

MORE INFORMATION

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.