3

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');

VarcharType

enter image description here

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_serveroption @server=N'ORAPROD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any idea why these would return different types?

  • Are you using the same provider in the linked server setup? Can you please post the linked server definitions ? – Spörri Jul 4 '17 at 9:11
  • Added definition to post. Both use the same provider – Gavin Jul 4 '17 at 9:32
6

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.

| improve this answer | |

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.