One of my clients contacted me for an issue where they reported that they see a problem with numbers when they are accessed via Oracle linked server. Their numbers were shown in scientific notation instead of plain digits.
Here is the query which they used to demonstrate the issue.
IF (OBJECT_ID('TEMPDB..#TempDbTempTable') IS NOT NULL)
BEGIN
DROP TABLE #TempDbTempTable;
END
GO
CREATE TABLE #TempDbTempTable (
OneColumn NVARCHAR(500)
)
GO
INSERT INTO #TempDbTempTable (Value)
select somenumber from
OPENROWSET('OraOLEDB.Oracle',
'(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=dbsrv.domain.com)
(PORT=1526)) (CONNECT_DATA=(SERVICE_NAME=ORAFIN)))';'scott';'tiger',
'select 1234567891011121314151617181920 AS somenumber from dual')
GO
SELECT * FROM #TempDbTempTable
GO
DROP TABLE #TempDbTempTable
GO
When the run query on one server it returns 1.23457e+030 but on another SQL Server, it was showing 1234567891011121314151617181920. It was interesting to note that both servers have same Oracle linked server. We tried various tests and found that numbers which are from 6 digits or above to scientific notation.
We started finding a difference and found that the version of SQL was different.
WORKAROUND/SOLUTION
While searching on the internet, I was able to find an explanation of this behavior from Microsoft knowledge base article 3051993. (FIX: The value of NUMBER type is truncated when you select data from an Oracle-linked server by using OLE DB provider)
To fix the issue, we needed to enable trace flag 7314. You can read the blog to know the steps to let the trace flag. SQL SERVER – What is Trace Flag – An Introduction
Other possible workarounds would be to modify the query and use to_char around the number. This would cause SQL to treat this as a character rather than a number.
Have you faced any such issue in your organization?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
