MSSQL - 255 characters limit in SELECT

[Johan_Argentina]

Hi all,
I have the following problem regarding a SQL query:
I have a field named "Comment" in my table, this field is a varchar(1000) type field. I'm using MSSQL2000.

If I run the following query:

select len(comment) from myTable where id='x'

it returns that the data is 800 characters long

but the problem is that if I run:

select comment from myTable where id='x'

it only returns the first 255 characters

Does anyone knows how to solve this limitation?

thanks for your help

[twilliamsen]

is there an option in MS SQL to set the field to text?

[Johan_Argentina]

Yes, I've already tried using text as data type, but still have the same problem, the SELECT only returns the first 255 characters

[twilliamsen]

I do not have SQL server installed here. What data types are available?

[bradgrafelman]

Varchar should be able to go up to 8,000 characters I believe.

A short Google shows that the issue may rely with the method that you're retrieving data from the database. What class/functions are you using?

[Johan_Argentina]

Yes, varchar is able to go up to 8,000 characters, in fact my field is 1000 characters long, and the data I have there is 800 characters, but when i try to retrieve it with a SELECT query it only returns the first 255

[Johan_Argentina]

I'm using a php function:

$sqlComment= "select comment from form";

queryComment=mssql_query ($sqlComment,$link);

and if i try executing that query in the SQL query analizer i get the same result

[bradgrafelman]

Are you sure you've tried converting the field to a TEXT field? Here's a user contributed note from the manual page for [man]mssql_query/man:

NOTE that the DB library that is internally driving these functions will NOT return more than 256 characters for data types varchar are char. After SQL Server 7 they allowed for lengths of up to 8000, but never updated the library. This drove me crazy for a day (all other connectivity worked from Java and C#, just not PHP). So, switch your data type to text if you wish to have lengths more than 256 characters.

Also, have you tried converting the data to a TEXT type in the SELECT query, i.e.

SELECT ..., convert(TEXT, comment) AS comment

EDIT: Just found another user contributed note that pertains to this issue:

To get round this I changed the column type from VARCHAR 8000 to TEXT, but then the output was trucated to 4096 characters.

To fix this I changed two values in PHP.INI:

mssql.textlimit = 16384
mssql.textsize = 16384

Now my text is trucated to 16384 characters, which is big enough for me - but you can apparently use a value as large as 2147483647.

Perhaps you should try making this .ini change before you go converting columns and such?

[chernyshevsky]

Johan Argentina wrote:

Hi all,
I have the following problem regarding a SQL query:
I have a field named "Comment" in my table, this field is a varchar(1000) type field. I'm using MSSQL2000.

If I run the following query:

select len(comment) from myTable where id='x'

it returns that the data is 800 characters long

but the problem is that if I run:

select comment from myTable where id='x'

it only returns the first 255 characters

Does anyone knows how to solve this limitation?

thanks for your help

The library used by the mssql extension is rather ancient. At this point it's two generations behind SQL Server. The 255 character limitation is a relict from SQL Server 7.

There a number of things you can do:

1. Cast varchar columns to text as others have suggested.
2. Find a copy of the extension that uses FreeTDS. I have never tried that myself as I couldn't find it.
3. Use the OLE-DB extension (written by yours truly). Setting oledb.mssql_aliases to on will make it mimick the mssql extension.
4. Use ODBC.
5. Use ADO through COM.