Fitting a quart into a pint pot

I received an interesting support email from a client yesterday. We'd migrated a very large and complex Access database to SQL Server and done some fairly extensive work on the Access front end application, moving slow running queries to sprocs.

The support request was relating to an Access query written by the client, running against the SQL database, that was returning the message "Scaling of decimal value resulted in data truncation". The query contained a calculated column which was the product of 2 decimal fields, a floor area multiplied by a percentage. This column was being summed as part of a GROUP By on the recordset.

She reported that the query didn't always return the error and using the Round function to limit the result of the calculation to 2 decimal places fixed the problem.

When I investigated a little further, I found that the original Numeric Single field in Access had been converted to a decimal(18,4) in SQL Server by the 'Microsoft SQL Server Migration Assistant for Access' tool, which has replaced the old Upsizing Wizard built into older versions of Access. I must own up to not having checked what every field had been mapped to by the tool.

The clue to the error is the 18 decimal digits precision (fixed with 4 decimal places) of the decimal field compared to the highest precision numeric data type is Access, which is a (floating point) Double with 15 decimal digits precision. At some point Access has to shrink (truncate) 18 digits coming from SQL Server down to 15. The query looks simple enough to be passed through to SQL Server for processing. Access decides to either lopp off the precision at the front end ('leading zeros') or at the back end (decimal places). My guess is that where the the result of the calculation is a similar scale across all records in the returning recordset, Access can chose to do one or the other across all records, and maintain all the decimal places without a problem. Where the recordset contains either very small decimal numbers or ones with a lot of decimal places mixed with some much larger numbers, a conflict arises and Access can't decide on a truncation scheme to apply across the board. By using the Round function to limit all results to 2 decimal places, it effectively frees up the precision the the left pf the decimal point. It is unlikely that the sum of any set of floor areas will be be greater than say 10^7, so there is easily sufficient scope to truncate from the left end of the number.

I now need to find time to reproduce the error and inspect the individual values in the recordset to confirm this.