0

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.

2
  • You may want to look at the source table value results with a simple select query without CAST or CONVERT and see if any values brought back as NVARCHAR to ensure it doesn't have some value that needs to be parsed such as commas and use REPLACE function to parse those to bring back a valid FLOAT data type. Otherwise, try with CONVERT rather than CAST and see if that makes any difference. Just a quick thought for some simple things to try. I'd say something or some value in some record at the source table may be causing this issue. Commented Aug 27, 2015 at 0:36
  • Hello, @PJMahoney, the problem is with the client not the source table. I can run the same query against the same data from another identical server without error Commented Aug 27, 2015 at 5:29

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.