SQL Server 2008 R2: Error Converting Data Type NVARCHAR to FLOAT with Oracle Linked Server

linked-serveroracle-10gsql-server-2008-r2

Upon executing the following query 

select value, cast(value as float) float_value 
from OPENQUERY([ORACLESERVER],
'select 3/5 as value from table')

I am getting the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float

.

I can run this same query on another server with the same Windows 2012 R2 OS, same SQL Server 2008 R2 Server and identical Oracle 12.1.0 client version and get back the correct result :

value   float_value
.6  0,6

The linked servers on both servers are identically configured.

The Oracle Server version is 

select * from OPENQUERY([ORACLESERVER],'SELECT * FROM V$VERSION')
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE   10.2.0.3.0   Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

Are there any other configuration options I have overlooked in SQL Server and/or the Oracle Client that may be causing these issues.

Best Answer

It is in fact a configuration issue in the Oracle Client

enter image description here

Set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and no more conversion error.

UPDATE

Why? Because 0.6 in French would be read as a character value whereas in English it remains numeric. 0,6 in French is 0.6 in English.

Defining NLS_LANG as American means that 0.6 stays numeric thus allowing the cast as float to occur without error.

Restart required.

Related Question