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:
[SQL Server]String or binary data would be truncated. |
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.
- 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:
/*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
- in case other fields created by coresystems are displayed, please contact coresystems support providing the query results.
Comments
0 comments
Article is closed for comments.