Linked server query from Oracle DB is truncating aggregate function results.
This issue was brought to my attention today. When returning a value from an Oracle database via a linked server query the aggregate function result, e.g., sum(amount), is being truncated. I tried this against other linked servers connected to Oracle databases. The value in the Oracle database is stored as a number with decimal precision and the attribute in SQL Server where this value is going is stored as numeric(38,4). I did discover a work around which was to explicitly cast the value with decimal precision in the openquery text, e.g., cast(sum(amount) as number(38,4)). However that doesn't really tell me when and why it started happening. I do know that it started sometime this year, but I can't identify a date since data was reloaded recently. My DBA isn't aware of any updates to the Oracle provider. Patch related? If anyone has any insight into this I would greatly appreciate the input. Microsoft SQL Server Enterprise (64-bit) v 11.0.5548.0