Post reply

can't insert more than 255 characters into a colum

  • September 5, 2002 at 12:09 pm

    #78735

    Why can't I insert more than 255 characters?

    I run the following INSERT statement and I still am not able to see more than 255 characters of the text that inserted:

    INSERT TestTable (Comments) VALUES('xxxxXXxx')

    NOTE: The xxxxXXxx above is a string containing more than 255 characters.

    Also, The TestTable Comments field is a data type varchar(4000).

    I'm running SQL Server Standard Edition on an Windows2000 box.

    Help please, my email is rcoston@colstor.com

    Thanks...

  • September 5, 2002 at 1:22 pm

    #436017

    Hi,

    How are you inserting and retrieving this value. Is it directly through the query analyser or from your program? Some programing languages have a limit of 255 characters on the length of the string variables.

    Suada

  • September 5, 2002 at 1:40 pm

    #436018

    Thanks for the quick reply. I'm using the Query Analyzer tool and I'm running the following SQL statement:

    INSERT TestTable(Comment) VALUES('xxxXXxx')

    Note: The string xxxXXxx is a string of more than 255 characters. I've also tried to change the column to data type text but that still doesn't seem to help at all. Is there a setting in SQL2000 that I need to make in the master dB or something. Everyone else seems to be able to enter thousands of characters while I am getting truncated to 255.

  • September 5, 2002 at 2:33 pm

    #436022

    It is possible that your database compatibility was set to sql server 65 which I believe has a limit of 255 on the varchar fields. You can use sp_dbcmptlevel to test the compatibility version.

  • September 5, 2002 at 6:15 pm

    #436034

    I ran sp_dbcmptlevel and it returned 80, which I think means SQL2000 compatibility, right?

    Any other suggestions? This has stopped me cold during development. What am I doing wrong?

  • September 5, 2002 at 6:30 pm

    #436036

    If I was you I would double check to ensure that the string is truncated when it is stored rather than when it is displayed. Try doing

    Select len(comments) from testtable

    Aside from that I have no other suggestions.

  • September 5, 2002 at 7:06 pm

    #436037

    Wow, thanks. It appears that Query Analyzer only shows 255 characters, when I did the len(comments) as you suggested it returned 420, the exact length of the string I attempted to insert.

  • September 6, 2002 at 3:26 am

    #436057

    QA by default only displays 255 charcters max per column. However, you can change this by going to the menu Tools/Options then on the dialog that appears choose the Results tab and change the value for Maximum Characters Per Column to a higher value. Then Apply and OK. Now it should show you the full 420 characters as long as the Maximum Characters is equal or higher than that.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

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

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