INF: Display Limitations for TEXT or IMAGE Columns

• Microsoft SQL Server versions 4.2x, 6.0, and 6.5

SUMMARY

By design, ISQL/W and the SQL Query Tool only display 255 characters when reading TEXT and IMAGE data. Setting TEXTSIZE does not make any difference. This default was set to reduce the likelihood of Windows 95-based text- drawing APIs and graphics drivers exceeding their character-per-line maximum. ODBC or DB-Library applications should not have any problems getting TEXT or IMAGE data beyond 255 characters.

The Isql.exe console application does not limit the output of TEXT or IMAGE data to 255 characters.

MORE INFORMATION

To retrieve all TEXT or IMAGE data from a record, use READTEXT in a loop to read the data out in chunks, as in the following example.

Sample Table Schema

   create table text_tbl
   (
     textstring text null,
     tid int                -- some unique record ID
   )

Loop to Retrieve TEXT Data Out in Chunks of 250 Characters

NOTE: The database must have the 'SELECT INTO/BULK COPY' option enabled.

   declare @txt_ptr   varbinary(16), -- Pointer to TEXT data
           @txt_len    int,          -- Remaining TEXT data length thru
   each loop
           @txt_offset int,          -- Offset into TEXT data
           @txt_chunk  int           -- Length of TEXT data retrieved thru
   each loop

   -- Retrieve pointer to TEXT data
   select @txt_ptr=textptr(textstring) , @txt_len=datalength(textstring)
     from text_tbl
     where tid=2

   -- Initialize loop parameters
   select @txt_offset=0,@txt_chunk=250

   while @txt_len > @txt_chunk
   begin
    readtext text_tbl.textstring @txt_ptr @txt_offset @txt_chunk
    select @txt_offset = @txt_offset + @txt_chunk, @txt_len = @txt_len -
   @txt_chunk
   end

   -- Retrieve remaining TEXT data
   readtext text_tbl.textstring @txt_ptr @txt_offset @txt_len