Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server.

If I run the following query 2008 returns 1.23 as a VARCHAR but 2016 returns it as a float

SELECT * INTO #tst FROM OPENQUERY(JOHPROD,'SELECT 1.23 FROM dual')
SELECT t.Name,* FROM tempdb.sys.columns c
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE [object_id] = OBJECT_ID(N'tempdb..#tst');

Both instances are pointing to the same Oracle server and both using the same Oracle ODBC driver.

Both linked servers have this same definition...

```
EXEC master.dbo.sp_addlinkedserver @server = N'ORAPROD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORAPROD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAPROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORAUSER',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_servero

This behaviour has changed in recent updates. See
FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider


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 7314 that would start treating such values
as numeric (38, 10).

However I'm a bit surprised you are getting a float instead of a double \ numeric since that seems to contradict the article somewhat, so I'm not sure it explains your exact situation.

StackExchange Database Administrators Q#177936, answer score: 6