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

Question

Post reply

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

MO-622821

SSC Veteran

Points: 205
More actions
May 5, 2008 at 12:57 pm

#95923

Initialley when I was tryed to insert values in one of the table, during insert I was getting this error message.
Error Message
String or binary data would be truncated.
The statement has been terminated.
So as a solution I entered following statement.
SET ANSI_WARNINGS OFF
Then I was able to insert records into table, but now when I try to select records using customercode I am getting this error.
One more thing is that if I used customerid to select records there is no error message....
select * from customertable where customercode = 01042
Here is the error message.
Conversion failed when converting the varchar value 'XX' to data type int.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

What about trying it like this:

select * from customertable where customercode = '01042'

If customercode is a string, you need to wrap the number being passed so that it's a string too. If customer code is a number, then it should be 1042, no zero.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MO-622821 SSC Veteran Points: 205 More actions · Answer 2

Thank you statement works for me by putting quotes, but my I am still wondering I never received this error message before if I forget to put quotes for string variable.

Thank you again.

😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

May 6, 2008 at 7:34 am

#811189

Could we see the DDL for the table?

😎

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

You said above that you ran the code against CustomerId and it worked. I'll bet CustomerId is an integer column.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MO-622821 SSC Veteran Points: 205 More actions · Answer 5

Here is table structure.

CustomerIDintno4

CustomerCodevarcharno25

CustomerNamevarcharno100

Addressvarcharno50

Cityvarcharno10

Provincevarcharno25

ZIPvarcharno10

Telephonevarcharno10

Emailvarcharno50

Regionvarcharno20

Districtvarcharno50

Manufacturervarcharno10

CustomerTypeIDintno4

ProductIDintno4

projectIDintno4

WorkPhonevarcharno10

CellPhonevarcharno10

isCertifiedProductbitno1

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

It worked before because you didn't have any records with CustomerCode='XX'. Now you do.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ed B Hall of Fame Points: 3669 More actions · Answer 7

Whats the function to convert roman numerals to int? 😉