Oracle Number data type conversion to SQL Server

Transact-SQL
1

Hi Team,

As part of server migration we came across strange behaviour in SQL 2012 and SQL 2016 versions. please help to understand why SQL converting into different data types in different SQL versions.

In SQL Server 2016:
SELECT * into test
FROM OPENQUERY(LS_SOURCE,'SELECT POLICY_ID FROM POLICY where POLICY_ID = 37468')

sp_help test

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
POLICY_ID float no 8 53 NULL no (n/a) (n/a) NULL

SQL Server 2012:
SELECT * into test
FROM OPENQUERY(LS_SOURCE,'SELECT POLICY_ID FROM POLICY where POLICY_ID = 37468')

sp_help test

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
POLICY_ID nvarchar no 768 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

The data type for policy_id column in source server table POLICY is Number NOT NULL. Could you please help to understand why

Number colummn converted to nvarchar (768) in SQL 2012 server and float in SQL 2016 in SQL Server table (test)

why collation is SQL_Latin1_General_CP1_CI_AS in SQL 2012 server and NULL in SQL 2016 for newly created table column (test.policy_id)

2

Are you using the same provider on both linked servers? In SQL Server, numeric or decimal are closest to the Oracle NUMBER data type. I would explicitly create the table in SQL Server so you aren't depending on a driver to map your data types.

3

Thank for your reply. Yes we are using same provider "OraOLEDB.Oracle" in both linked servers.

But we didn't understand why Number column in source converted to nvarchar (768) in SQL 2012 server and float in SQL 2016 in SQL Server table.

4

Apparently that provider is not providing data type metadata correctly.