Upgrade to SAP Business One 8.81 patch 10 or later can fail with following error raised by SQL Server (can be found in upgrade log file):
[SQL Server]String or binary data would be truncated.
This can be caused by incorrect definition of length limit for User-Defined Field (UDF), when string stored in database contains more characters then allowed by length limit. In such case, string length limit for the affected UDF needs to be increased.
Following SQL query will help you to detect such UDFs. Please note that the query execution can take from minutes to several hours depending on number of UDFs in your database:
----------------------------------------------------------------------------------------------------
declare @TableID nvarchar(20);
declare @AliasId nvarchar(18);
declare @EditSize smallint;
declare @RealSize smallint;
declare @statement nvarchar(max);
declare @params nvarchar(max);
declare alfa_udfs cursor for
select TableID, AliasID, EditSize from cufd where TypeID = 'A';
open alfa_udfs;
FETCH NEXT FROM alfa_udfs
INTO @TableID, @AliasId, @EditSize;
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM sys.tables WHERE name = @TableId)
BEGIN
set @RealSize = 0
set @params = '@RealSizeOut smallint output';
set @statement = 'select @RealSizeOut = max(len(U_' + @AliasID + ')) from [' + @TableId + '] where U_' + @AliasID +' is not null';
EXECUTE sp_executesql @statement, @params, @RealSizeOut = @RealSize OUTPUT;
if @RealSize is not null
BEGIN
if @EditSize < @RealSize
BEGIN
select @TableID As TableName, 'U_' + @AliasId As UDFName, @RealSize As RealSize, @EditSize As DefinedSize
END
END
END
FETCH NEXT FROM alfa_udfs
INTO @TableID, @AliasId, @EditSize;
END
CLOSE alfa_udfs;
DEALLOCATE alfa_udfs;
----------------------------------------------------------------------------------------------------
Results:
Results will contain a table, showing the list of UDFs affected by this issue. If there is no result, you are not affected by this issue.
Column TabeName shows table, which contains the UDF.
Column UDFName shows the name of affected UDF.
Column RealSize shows the length of longest string stored in UDF.
Column DefinedSize shows length limit from UDF definition.
Extend the UDF field length limit in SAP Business One.
1. Go to Tools -> Customization Tools -> User-Defined Fields - Management
2. Search for a UDF with Title as UDFName from query results (without 'U_')
3. Select the UDF and click 'Update'
4. Edit the Length value, set it to RealSize from query results
5. Update the UDF definition
Once you do this for all UDFs from results, please try to run upgrade again.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.