Post reply

TRUNCATED Column value at MS SQL 2008

  • September 9, 2015 at 3:18 am

    #399008

    Hi!

    I just have a question regarding storing values to a column in ms sql 2008.

    Why is it that the value I inserted at the column is truncated when selected in a query.

    The column for this is created to accept max. values.

    -> Message VARCHAR(MAX) NULL

    The string which I need to insert is a combination of characters with a length of 14,720.

    I have been researching at the net and even forums to resolve this.

    According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)

    Kindly advise!

    Thank you very much.

  • September 9, 2015 at 3:29 am

    #1825342

    MAX columns accept data up to 2GB of text.

    If the text is truncated, it may be due to some function you're applying to the data before inserting. For instance, REPLICATE is limited to 8000 chars.

    Another possibility is that you're not seeing the whole text in your column due to client settings. Try selecting LEN(column) or DATALENGTH(column) to see how many characters are actually stored in there.

    -- Gianluca Sartori

  • September 9, 2015 at 3:34 am

    #1825344

    Probably because of your Management Studio settings.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 9, 2015 at 9:41 am

    #1825444

    Yes, the string is inserted at the column correctly, therefore the value is not truncated the moment

    I insert it to column. However, when I select the column (select column_name from table) the display of the value is not shown completely.

    Can I adjust the setting of the display of column so that everything inserted will be shown?

    Thanks a lot.

  • September 9, 2015 at 9:43 am

    #1825446

    Hi,

    Is there a way to adjust the value for that?

    Let's say I need to increase it up to 100,000?

    Thanks a lot.

  • September 9, 2015 at 10:01 am

    #1825457

    subscriptionemail (9/9/2015)

    Hi,

    Is there a way to adjust the value for that?

    Let's say I need to increase it up to 100,000?

    Thanks a lot.

    No you can't set it to 100,000 in SSMS. If you run a select statement the data is still there it just doesn't display it all in SSMS because 100,000 characters is ridiculous in a grid.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 9, 2015 at 10:25 am

    #1825467

    😀 yes, I have realized that it is really ridiculous, and a customer will not give a lot of time to read everything at the column 😉

    Anyway, I will just review the length of the serialized data inserted to the column and see if the max. is already enough.

    Thanks guys for all the answers.

  • September 9, 2015 at 10:31 am

    #1825469

    subscriptionemail (9/9/2015)

    😀 yes, I have realized that it is really ridiculous, and a customer will not give a lot of time to read everything at the column 😉

    Anyway, I will just review the length of the serialized data inserted to the column and see if the max. is already enough.

    Thanks guys for all the answers.

    The data is in the column. It just doesn't display in SSMS. You have to remember that SSMS is just a client tool for the database and that is a limitation of that piece of software.

    I assume your customer is not reading this in SSMS so just make sure that whatever front end you have will let them read it. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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