Post reply

Error while converting TEXT datatype to varchar

  • 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.

  • April 13, 2010 at 9:10 am

    #1150797

    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

  • April 13, 2010 at 10:40 am

    #1150898

    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.

  • April 13, 2010 at 11:47 am

    #1150938

    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

  • April 14, 2010 at 12:31 am

    #1151139

    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.

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

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