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:
Check if your database is affected by the issue by running the attached Select Query (from Note 1645439)
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.
- use the company DB/restore DB Backup
run the attached Select Query (from 1645439) to see if your DB is in general affected
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:
- in case other fields created by coresystems are displayed, please contact coresystems support providing the query results.