3

I have a SQL Server grabbing data from a linked Oracle server inside a SP.

On my development server, EF6 insists the SP is returning a double datatype for one of the columns, but in prod, it insists the same column is a decimal.

The SQL Server version is slightly different:

  • Prod: 11.0.5582.0
  • Dev : 11.0.5613.0

There are no (obvious) differences in how the linked servers are configured (i.e. I've compared the connection properties in SSMS).

Being a new feature, the production database has very few (zero, even) rows in the database, while the dev server has many. I have a feeling that might be related, because I recall having to change the datatype in the middle of development (though I didn't think much of it at the time).

What might be the problem here?

  • 1
    There's potential that the data being returned fits within what your EF defines as a decimal, rather than a double. IT could mean that the level of precision being returned is higher than you expect (something greater than 16 significant figures). Have you tried casting the results to something smaller, like a numeric(10,2)? – Nic Oct 14 '16 at 4:27
  • @Nic The actual data in the development database are whole numbers in a narrow range of about 10 and 10,000. That is definitely the workaround that comes to mind. – mootinator Oct 14 '16 at 4:45
  • As it turns out, the Oracle column is defined as a NUMBER with no precision or scale, which causes the numbers to be stored as floating-point. Seems like there might be a bug somewhere where (like me) something is expecting a NUMBER column to always return something decimal-like. – mootinator Oct 14 '16 at 5:22
6

The observed difference in behavior is due to the difference in patch levels between the two servers.

Per the resolution of KB3051993, which is included in Cumulative Update 6 For SQL Server 2012 SP2):

After you apply this fix, NUMBER values with unknown precision/scale are treated as double values with OLE DB provider. If the precision is important and the range of the values is not large enough, you can enable the new Trace Flag 7311 that would start treating such values as numeric (38, 10).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.