Oracle 11g链接到PaaS上的SQL 2016，返回Number数据类型的指数值 [英] Oracle 11g linked to SQL 2016 on PaaS, returns exponential values for Number data type
问题描述
我们有旧的SQL Server 2008 R2，其中Oracle 11g DB被添加为链接服务器。
我们通过存储过程调用在SQL中使用OpenQuery访问oracle数据。
我们还使用了各种函数，如Convert（）和FOR XML PATH（''）
一切正常.│
但是当这个SQL服务器已经迁移到PaaS上的SQL Server 2016，因为很多值，特别是oracle中有NUMBER数据类型的值开始以指数值的形式返回，这导致了很多问题。 br />
使用openquery从oracle中选择记录没有任何问题。
但对于某些具有Number数据类型的columsn，当应用如下函数时，我们得到指数值
转换（nvarchar（20），Amt）作为金额
所以像20000352这样的值被返回为 2.00004e + 007
但同样的功能并没有给另一个有价值的栏目带来任何问题e 2955.68按原样返回。
另外，当存储过程结束时，我们有FOR XML PATH（''），然后是所有NUMBER数据类型列返回指数值
我明白使用Cast操作可以解决这个问题。
它必须在所有方面完成访问链接服务器的所有存储过程中的NUMBER个字段。需要付出太多努力。
b $ b但我怀疑的是，如果它在SQL Server 2008 R2中运行，为什么它不能在PaaS上的SQL server 2016中工作。
正在使用的数据库版本如下：
Oracle Database 11g Enterprise版本11.2.0.3.0 - 64位
Microsoft SQL Server 2016（SP2）（KB4052908） - 13.0.5026.0（X64） 2018年3月18日09:11:49 版权所有（c）Microsoft Corporation Windows Server 2012 R2 Datacenter 6.3上的标准版（64位）< X64> （Build 9600：）（管理程序）
我还找到了一个Oracle链接服务器给出的页面NUMBER数据类型的问题并提供了修复。
但是，我的情况并不完全相似，所以修复对我不起作用。
从链接服务器读取的数据在生产服务器上返回不同的数据类型
https://dba.stackexchange.com/questions/152249/data-read-from-linked-server-returns-不同数据类型的生产服务器
FIX：从Oracle链接的服务器中选择数据时，NUMBER类型的值被截断使用OLE DB提供程序
https://support.microsoft.com/en-u s / help / 3051993 / fix-the-value-of-number-type-is-truncated-when-you-select-data-from-a
所以，如果有人知道SQL 2016的解决方案可以解决上述问题，请告诉我吗？
We had old SQL Server 2008 R2 where Oracle 11g DB was added as linked server.
And we were accessing the oracle data using OpenQuery in SQL via stored procedure calls.
We also used various functions like Convert() and FOR XML PATH('')
All was working fine.
But when this SQL server was migrated to SQL Server 2016 on PaaS,
Suddenly a lot of values particularly the ones with NUMBER data type in oracle started getting returned as exponential values, which is causing a lot of issue.
Simly selecting the records from oracle using openquery is not having any issue.
But for some columsn with Number datatype when function like below is applied, we are getting exponential values
convert(nvarchar(20), Amt) as Amount
So a value like 20000352 gets returned as 2.00004e+007
But same function didn’t give any issue for another column where the value 2955.68 got returned as it is.
Additionally when at the end of the stored procedure we have FOR XML PATH(''), then all the NUMBER data type columns get returned as exponential values
I understand that using a Cast operation could solve this problem.
And it will have to be done in all NUMBER fields in all the stored procedures that access Linked Server. it will require too much efforts.
But my doubt is that if it was working in SQL Server 2008 R2 why it isnt working in SQL server 2016 on PaaS.
The Database Versions being used are as below:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)
Also I found one page where Oracle Linked server had given an issue for NUMBER datatype and a fix was provided.
However, my case is not completely similar to that so that fix wouldn’t work for me.
Data read from linked server returns different data-type on production server
https://dba.stackexchange.com/questions/152249/data-read-from-linked-server-returns-different-data-type-on-production-server
FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider
https://support.microsoft.com/en-us/help/3051993/fix-the-value-of-number-type-is-truncated-when-you-select-data-from-an
So please let me know if anyone is aware of a simiarl fix for SQL 2016 to solve above problem?
推荐答案
您好Ashish Thorat，
您可以尝试启用跟踪标志7314，它将开始将这些值视为数字（38,10）。请参考
SQL SERVER - 什么是Trace标志 - 简介和
Trace Flags 。
Hi Ashish Thorat,
Could you try enabling trace flag 7314 that would start treating such values as numeric (38, 10). Please refer to SQL SERVER – What is Trace Flag – An Introduction and Trace Flags.
这篇关于Oracle 11g链接到PaaS上的SQL 2016，返回Number数据类型的指数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
