MSSQL Text Column Problems

aborg

I'm having diffuculties wtih text while using PHP4 and MSSQL 7. I have a text string that consists of updated notes on clients. When these were just starting out and relatively small we had no problems. Now that the notes are getting large I'm finding that the text is being truncated when its either placed in the database or retrieved from it. Either way, something is not working as I expect it to. I'm used to working with PHP under Linux using MySQL and this is a learning experience for me.

Someone please explain how I can place large (by large I mean anywhere from 5 sentences to 100 paragraphs) amounts of text into the database, and retrieve it intact with no truncation.

Aaron Borg
Web Developer
Pathology Associates Medical Labs

aborg

The more I play around with this the more I think I'm only writing 256 characters to the table.... Using MULTIPLE different ways of getting the text out of the table I am never able to get more than 256 by using the SQL Server Query Analyzer. Should this give me EVERYTHING is the test field??:

SELECT test
from clientprofiles
where siteid = '667'

I know I've written exactly 300 chars to the field but I never get more out than 256... so I'm beginning to belive that I'm not even writing more than 256.. Anyone?

Anon

You are not alone. I have the same problem too. I suppose that it is a bug from the mssql library.

Anon

Please write your SQL statement as:
select convert(TEXT,someTextField) as someTextField from someTable

Anon

How about updating the database?

aborg

I must be missing something here. Could it be that I am not even WRITING more than 256 chars to the database with this statement? Look at what I am getting when I write 300 chars to the column:

SET TEXTSIZE 100000
DECLARE @noteptr varbinary(16)
SELECT @noteptr = TEXTPTR(test2)
FROM clientprofiles
WHERE siteid = '667'
UPDATE clientprofiles
SET test2 = '1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/'
WHERE siteid = '667'
select convert(TEXT,test2) as test2 from clientprofiles where siteid = '667'

test2:

1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/1111111111111111111111111111111111111111111111111/111111

(1 row(s) affected)

As you can see, I am WRITING 300 chars, but still only getting 256 back out of it. What the heck??????