Decimal numbers are truncated to integers when exporting to LEAN ERP or other Oracle based system

Symptom

When you execute a query to select data from an Oracle-linked server by using OLE DB provider in Microsoft SQL Server 2012 or SQL Server 2014, the values in the NUMBER type column may be truncated in the query result.

For instance when exporting BOM to LEAN ERP system quantity for a BOM row is number. When providing decimal number for quantity it may be truncated to integer (whole number) in system.

Cause

This issue occurs because, the Oracle NUMBER type with non-declared precision/scale may not have a clear 1:1 mapping to a SQL Server data type. Before SQL Server 2012 SP2 CU2, SQL Server maps such values to strings to make sure that no loss of precision happens. However, that could lead to incorrect results if the query required sort order on such values. In SQL Server 2012 SP2 CU2, the mapping is changed to numeric (38, 0) to allow for representation of big integer keys which some database schemas are using NUMBER without precision/scale for. This leads to regressions when non-integer values are needed.

Solution

Microsoft has fixed this problem in SQL Server updates. To download necessary update see KB3051993.