Fetching SAP table data throws Error 8114

Question

I'm trying to update SQL data from SAP data (same happens with insert). I'm always getting runtime error

SQL error 8114

The reason for the exception is:

Database error text: "Error converting data type nvarchar to numeric."

All fields of 'gs_sap_table2' are type CHAR100. I've tried many things like converting some fields of my structure from CHAR 100 to decimal 18,5 and fill all the 'NOT NULL' SQL fields with values but problem remains.

I'm posting you my ABAP code and a picture of the SQL database fields:

      EXEC SQL.
        CONNECT TO :gv_connection
      ENDEXEC.

      EXEC SQL.
        UPDATE STOCKKEEPINGUNITS
           SET ProductId           = :GS_SAP_TABLE2-PRODUCTID,
               CreatedOn           = :GS_SAP_TABLE2-CREATEDON,
               UpdatedOn           = :GS_SAP_TABLE2-UPDATEDON,
               UPC                 = :GS_SAP_TABLE2-UPC,
               AvailabilityType    = :GS_SAP_TABLE2-AVAILABILITYTYPE,
               Stock               = :GS_SAP_TABLE2-STOCK,
               Currency            = :GS_SAP_TABLE2-CURRENCY,
               TaxClass            = :GS_SAP_TABLE2-TAXCLASS,
               RetailCurrentPrice  = :GS_SAP_TABLE2-RETAILCURRPRICE,
               Weight              = :GS_SAP_TABLE2-WEIGHT,
               MeasurementUnitId   = :GS_SAP_TABLE2-MEASUREMENTUID,
               NameL1              = :GS_SAP_TABLE2-NAMEL1,
               NameL2              = :GS_SAP_TABLE2-NAMEL2,
               ShippingCost        = :GS_SAP_TABLE2-SHIPPINGCOST
        WHERE SKUId = :GS_SAP_TABLE2-SKUID
      ENDEXEC.

      EXEC SQL.
        COMMIT
      ENDEXEC.

      EXEC SQL.
        DISCONNECT :gv_connection
      ENDEXEC.

you should always put MSSQL version, in data conversion tasks it matters — Suncatcher, Nov 10 '22 at 02:55

score 3 · Answer 1 · answered Oct 31 '22 at 11:05

The error tells you that there is an nvarchar value and it is being attempted to be implicitly converted into a numeric type. So, the error happens either in the SET or the WHERE clause. Since in the WHERE clause from the two operands one is the field, SKUId, which is a varchar and not an nvarchar nor a numeric type, it is clear that the error happens somewhere in the SET clause.

In the SET clause you assign values to fields. Conversion from nvarchar to numeric happens in these assignments if and only if the right-hand-side (i.e. the value) is nvarchar and the left-hand-side (i.e. the field) is numeric.

So, in order to fix your issue, you will need to

create a list for yourself containing all numeric fields in the SET clause that receive some value
make sure that the value (right-hand-side) of the assignment operation for each field is converted into a numeric value of the exact type your field expects

Fetching SAP table data throws Error 8114

1 Answers1