INF: Display Limitations for TEXT or IMAGE Columns

Last reviewed: November 20, 1997
Article ID: Q177067
The information in this article applies to:
  • 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


Additional query words: readtext dblib db-lib
Keywords : SSrvISQL kbusage
Version : WINDOWS:4.2x 6.0 6.5
Platform : WINDOWS
Issue type : kbinfo
Solution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.