Oracle to SQL Server Linked Server Data Type Discrepancy

entity-frameworklinked-serveroraclesql-server-2012

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?

Best Answer

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).

Related Question