Error while converting TEXT datatype to varchar

Question

Post reply

Error while converting TEXT datatype to varchar

somansam

Old Hand

Points: 300
More actions
April 13, 2010 at 8:31 am

#156864

I am getting error as mention below while alter the colum from TEXT data type to Varchar data type
"Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated."
I have check whether any record is present which has length more than 525.
So please help to solve the issue.

Viewing 5 posts - 1 through 4 (of 4 total)

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

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

Have you found the size of the largest value in your TEXT field? Try:

select len(max(yourtextfieldname))

from yourtablename

Also, changing the datatype of the TEXT field to VARCHAR(MAX) will allow a character maximum length of 2,147,483,645.

_________________________________
seth delconte
http://sqlkeys.com

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 2

I think Seth meant to say,

Select MAX(DATALENGTH(yourtextfield)) from yourtablename

The LEN function won't work on TEXT type fields. You need to use DATALENGTH.

Rob Schripsema
Propack, Inc.

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

Rob Schripsema (4/13/2010)
I think Seth meant to say,
Select MAX(DATALENGTH(yourtextfield)) from yourtablename
The LEN function won't work on TEXT type fields. You need to use DATALENGTH.

Thanks. 🙂

_________________________________
seth delconte
http://sqlkeys.com

somansam Old Hand Points: 300 More actions · Answer 4

I have used same to check wheather any data prsent which has lengthe more than 525 and after executing the update I have tried to alter table

but I am getting same error.