Error: SQL Server String or binary data would be truncated

Question

Error: SQL Server String or binary data would be truncated

Navigation

#1 by (2 votes)

2

I have the variable @numero1 which has the following result:

020000000000720000018

But at the time of the update it gives the error:

SQL Server String or binary data would be truncated

declare @g1 varchar(50)
declare @numero1 varchar(50)
set @g1 = SUBSTRING(@vNossoNumero, 1, 2) + REPLICATE('0', 9) + 
SUBSTRING(@vNossoNumero, 3, LEN(@vNossoNumero))
set @numero1 = left(@g1,Max(LEN(@g1))-2)

print @numero1 // - dá erro nessa variável

update DUPLICATA set DupNossoNumBco=@numero1 where DupNum=(  
select ParcDocFinDupNum from PARC_DOC_FIN   
where EMPCOD      = @EMPCOD  
AND  DOCFINCHV     = @DOCFINCHV  
AND  PARCDOCFINSEQ    = @PARCDOCFINSEQ  
AND  PARCDOCFINDESMPAG   = @PARCDOCFINDESMPAG  
)  

END

sql sql-server

asked by anonymous 29.08.2018 / 19:44

1 answer

How to print the repeated name only once? How to resolve a synchronization with the server when there is a connection crash on Android?

score 2 · Accepted Answer

The problem is that your DupNossoNumBco field is only 20 (according to the information you provided in comment) and the @numero1 variable reaches 28 characters:

-- @vNossoNumero = '020000000000720000018'
DECLARE @g1         VARCHAR(50)
DECLARE @numero1    VARCHAR(50)

SET @g1         = SUBSTRING(@vNossoNumero, 1, 2) + REPLICATE('0', 9) + SUBSTRING(@vNossoNumero, 3, LEN(@vNossoNumero))
SET @numero1    = LEFT(@g1, MAX(LEN(@g1)) - 2)

PRINT LEN(@numero1)
-- imprime "28"

For this you have two solutions, or increase the size of the field to the one that best fits, being sufficient to support the @numero1 variable, or, if you can not even change the size, make a SUBSTRING at the moment what to do UPDATE :

SET DupNossoNumBco = SUBSTRING(@numero1, 1, 20)