Error: SQL Server String or Binary data would be truncated

Asked

Viewed 528 times

2

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

020000000000720000018

But at the time of update gives 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 database sql-server

  • This error is related to the size of varchar you will receive the update. You can ask the question the size of the field DupNossoNumBco?

  • It has the size 20

  • So you already found the answer. Your variable has a 21-character content. =)

  • Something strange/curious is the use of max()

  • The result of the variable @numero1 turns out to be 28, so trying to update a field of size 20 with 28 characters gives error.

1 answer

2


The problem is in the fact of its field DupNossoNumBco be only large 20 (according to the information made available in comment) and the variable @numero1 reach out to 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 has two solutions, or increases the size of the field to the one that is most suitable, being enough to support the variable @numero1, or, if you can’t even change the size, make a SUBSTRING the moment you do UPDATE:

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

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.