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

Asked

Viewed 8,934 times

1

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

UPDATE TESTE SET ID = 9250 WHERE ID = 1234

Error is returned below:

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

However the error occurs only in one of the servers with SQL Server 2008 R2 the other server that has the identical version does not occur the error, however I could not identify if it has any parameter different from one server to the other. Has anyone ever seen this kind of mistake???

sql-server
  • 1

    If the column and string vc should assign a string value to it, in this case you are trying to assign an integer value instead of 9250 try '9250'.

  • Dener, if I perform this process works, however I wonder why one instance accepts I insert the whole value and the other does not.

1 answer

1


You can reproduce the problem like this.

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 the column ID for int to do the WHERE. Failure if a value cannot be converted.

So it is very likely that the server that shows the error has a non-numeric value in the ID column.

Browser other questions tagged sql-server

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