MS SQL text field scrambled

[jeremy_brooks]

so I have this MS SQL DB with about 30 cols, 3 of which are of type 'text'. If I try to pull more than one of these text fields at a time in a single select * cursor, the 2nd and 3rd field results come out as gibberish, regardless of how much data is in each field. Any ideas?

[fleymingmasc]

My experience with MS SQL is that any sort of BLOB field, including Text, is going to cause problems. However, starting in SQL 7, VARCHARs may be up to 8000 bytes long. This may solve your problem right there.

If you need to store something longer than that, try this sort of layout in a table:

record_id INT,
sequence_id INT,
text_fragment VARCHAR(8000)

Break up your text into 8000-byte sections and store them with an incrementing sequence_id. Then you can do a
"SELECT text_fragment FROM table WHERE record_id = $recordid ORDER BY sequence_id"
and then concatenate the results.

BLOBs are stored in a separate file structure than other field types; the placeholder in the physical record is a long pointer to the BLOB. Therefore you can't do SELECTs on a BLOB. Using VARCHAR takes care of this problem.

[fleymingmasc]

Whoops, my mistake. I meant to say that you can't have a WHERE clause referring to a BLOB. For example, if text_blob is a "text" field, you can't run this query:

SELECT * FROM table WHERE text_blob LIKE '%My Special Text%'

[steved]

The work around is to do a different select statement for each of the text columns.