хорошая sql , может надо кому
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;