Have you ever encountered a situation where numbers accessed via an Oracle linked server in SQL Server are displayed differently? In this article, we will explore a common issue related to number formatting and discuss possible workarounds.

Recently, one of my clients reported a problem with numbers when accessing them via an Oracle linked server. They provided a query 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 running this query on one server, the result was displayed as 1.23457e+030, while on another SQL Server, it showed 1234567891011121314151617181920. Both servers had the same Oracle linked server configured. After conducting various tests, we discovered that numbers with 6 digits or more were being converted to scientific notation.

Upon further investigation, we found a Microsoft knowledge base article (3051993) that explained this behavior and provided a solution. The issue could be fixed by enabling trace flag 7314. You can find detailed steps on how to enable this trace flag in our blog post “SQL SERVER – What is Trace Flag – An Introduction”.

Alternatively, modifying the query and using the TO_CHAR function around the number can also resolve the issue. This forces SQL Server to treat the number as a character rather than a numeric value.

Have you encountered a similar issue in your organization? Let us know in the comments below!