We have a middleware that replicates data from an Oracle (11g) database to a SQL Server (2012) using ODBC connection to the destination. One of the tables has a NUMBER data type on Oracle and given there is no exact equivalent on SQL Server, we have used DECIMAL (18,0) on the SQL Server side in hope of catching errors if the data actually has fractions. However we've noticed that replication works without any error while truncating the fractions from the numbers. We traced the queries coming from the middleware and there are bunch of sp_prepexec statements where parameters are defined as the target type (e.g. DECIMAL(18,0)) and values are already converted before reaching SQL server and hence no error despite having NUMERIC_ROUNDABORT ON on the SQL Server. Is this how ODBC supposed to work? Are there ways to force errors on these type of data type conversions?
If the parameter to the ODBC prepared statement is defined as
I think you're mistaken, though. From what I make of the
If you really can't pass the data as a decimal type, I think I'd pass it as a
Given that SQLserver's decimal datatype potentially has the same range as oracle's number, albeit with a fixed scale, your decision to limit yourself to (18,0) and trying to catch errors seems strange.
Shouldn't something like (38,20) suit your needs better, since you seem to be assuming a max of 18 natural digits anyway?
The optimal solution would seem to be analyzing the oracle data to decide the best decimal value to use in SQLserverm but I assume that option is not available?