can't insert more than 255 characters into a colum

Question

can't insert more than 255 characters into a colum

RCoston

Mr or Mrs. 500

Points: 585
More actions
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...

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

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

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 1

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

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 2

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.

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 3

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.

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 4

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?

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 5

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.

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 6

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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)