Loading

Error "Error converting data type varchar to float” Creating Extract of SQL Server Data Source

Udgivelsesdato: Jan 27, 2025
Beskrivelse

Issue

When attempting to extract an MS SQL Server data source, one of the following errors might occur:

 

An error occurred while communicating with data source <Data Source Name>.
SQL Server database error 0x80040E07: Error converting data type varchar to float.

 

or

 

[Microsoft][SQL Server Native Client 11.0][SQL Server] Error converting data type varchar to float 
Unable to create extract

 

Cause

This error occurs because MS SQL Server is unable to convert string values to a numeric value if the string contains the following:

  • Currency symbol such as $, £, or ¥. For example, "$123".
  • Commas. For example, "100,000".
  • A single character of: currency symbol, comma(,), decimal point (.), plus (+) sign, or minus (-) sign. For example, "$".
  • More than one period. For example, "1.1.0".
  • Incorrectly positioned plus (+) or minus (-) signs. For example, "2-".

 

Løsning

Use one of the following options, as appropriate to your data:

  • Create a calculated field to strip currency symbols and commas from your data before converting it. 
  • Ensure that characters such as the plus (+) sign, minus (-) sign and decimal point (.) are positioned in a valid number format. For example, plus and minus signs can only be used to the left of a number (For example, -2). 
  • If your string data values are currency, you can use rawSQL to cast the string to SQL Server's MONEY data type before casting to a number. For example:
    RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [<Your_String_Field>])

  

Vidensartikelnummer

001498347

 
Indlæser
Salesforce Help | Article