Conversion failed when converting the varchar value '????' to data type int

1

When performing UPDATE on a VARCHAR (4) column using an integer value in the SET clause.

UPDATE TESTE SET ID = 9250 WHERE ID = 1234

The following error is returned:

  

Conversion failed when converting the varchar value '????' to data type int.

However the error occurs only on one of the servers running SQL Server 2008 R2, the other server that has the identical version does not get the error, but I was unable to identify if it has any different parameters from one server to another. Has anyone seen this type of error ???

    
asked by anonymous 25.04.2016 / 22:53

1 answer

1

You can reproduce the problem as well.

CREATE TABLE TESTE
(
    ID varchar(4) NOT NULL
);
GO
INSERT INTO TESTE (ID)
    VALUES ('1234'), ('4567');
GO
UPDATE TESTE
    SET ID = 9250
    WHERE ID = 1234;
GO
TRUNCATE TABLE TESTE;
GO
INSERT INTO TESTE (ID)
    VALUES ('1234'), ('4567'), ('ABCD');
GO
UPDATE TESTE
    SET ID = 9250
    WHERE ID = 1234;
GO
  

Msg 245, Level 16, State 1, Line 27   Conversion failed when converting the varchar value 'ABCD' to data type int.

The system converts the values in column ID to int to make WHERE . Failed if a value can not be converted.

So it is quite likely that on the server that displays the error it has a non-numeric value in the ID column.

    
27.04.2016 / 12:02