Issue description

Upgrading to SAP Business One 8.82 PL 03 or higher fails due to incorrect definition of length limit for User-Defined Field (UDF), when strings stored in database contain more characters then allowed by length limit.

As per SAP Note 1227965 - UDF are created as nvarchar(max) in MS SQL 2005, since MS SQL 2005 all alphanumeric UDFs were created on SQL level as nvarchar(max); in SAP Business One itself those fields were created with a fixed length.

Whereas adding/updating content in those UDFs within SAP Business One respected the defined field length, the defined field length was ignored when adding/updating content in those UDFs via Add-ons (SDK) thus allowing values longer than the defined field length. As of  8.82 PL03 the defined field length is also respected when adding/updating content in those UDFs via Add-ons (SDK).

In addition, SAP has now redesigned the definition of alphanumeric UDFs from database type nvarchar(max) to nvarchar([UDF_size]) in SAP Business One. This redesign is described in SAP Note  1645439 - Upgrade may fail with error: String or binary data would be truncated .

Due to the above described changes, upgrading SAP Business One might fail to Version 8.81PL10 or higher. The actual Upgrade Check which the Upgrade Wizard performs does currently not include the Query attached to Note 1645439 as the query execution can take from minutes to several hours depending on the number of UDFs in the company database. Basically the Upgrade Check might come back ok, but the actual Upgrade will fail and the Upgrade log will state that:

Troubleshooting steps/tips

Check if your database is affected by the issue by running the attached Select Query (from Note 1645439)

Solution

BEFORE UPGRADING SAP BUSINESS ONE, UPGRADE CORESUITE COUNTRY PACKAGE TO AT LEAST VERSION 3.5!

In case you had started your SAP Business One Upgrade already, and you can no longer access your company Database to update coresuite country package, the following queries can help you to prepare your company database for a successfull Upgrade.

1. use the company DB/restore DB Backup 

2. run the attached Select Query (from 1645439) to see if your DB is in general affected 

3. in case the query results include the following three entries: U_PrnKey and U_NameId from the @SWA_LD_PRNDEF table, U_Key from @SWA_LD_PARAM run the corresponding Detect and Fix Query:

    

   /*DETECT QUERY IF U_NameId ERROR OCCURS:*/ SELECT   U_NameId   ,Code   ,Name   ,CreateDate FROM   [@SWA_LD_PRNDEF] WHERE  LEN(U_NameId) > 30   /*FIX QUERY FOR U_NameId:*/ UPDATE [@SWA_LD_PRNDEF] SET    U_NameId = LEFT(U_NameId, 30) WHERE  LEN(U_NameId) > 30       /*DETECT QUERY IF U_PrnKey ERROR OCCURS:*/ SELECT   U_PrnKey   ,Code   ,Name   ,CreateDate FROM   [@SWA_LD_PRNDEF] WHERE  LEN(U_PrnKey) > 1   /*FIX QUERY FOR U_PrnKey:*/ UPDATE [@SWA_LD_PRNDEF] SET    U_PrnKey = '0' WHERE  LEN(U_PrnKey) > 1       /*DETECT QUERY IF U_Key ERROR OCCURS:*/ SELECT   Code   ,Name   ,U_Key FROM   [@SWA_LD_PARAM] WHERE  LEN(U_Key) > 12   /*FIX QUERY FOR U_Key:*/ UPDATE [@SWA_LD_PARAM] SET    U_Key = LEFT(U_Key, 12) WHERE  LEN(U_Key) > 12

4. in case other fields created by coresystems are displayed, please contact coresystems support providing the query results.

Remarks