Microsoft KB Archive/177067

= INF: Display Limitations for TEXT or IMAGE Columns =

Article ID: 177067

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q177067



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: kbinfo kbusage KB177067

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.