question

thesqlguyatl avatar image
thesqlguyatl asked

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
sql-server-2012tsqloraclelinked-serveropenquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.