FreeTDS Development Group

[Brian Bruns <camber AT ais.org>]

James,

maybe I can expand on this a bit:

Q. Why are my char, varchar, whatever fields being truncated at 256
characters.

A. The 4.2 and 5.0 versions of the TDS protocol use a 1 byte field to
specify the length of the column.  Thus the maximum allowable size was 255
characters.

With the introduction of SQL Server 7.0, Microsoft increased the max to
8000(?) bytes. However when accessing these columns via TDS 4.2, it *must*
truncate them to 255 characters.

There are two possible solutions:

a) use a protocol that is designed to handle this, TDS 7.0 or 8.0 (8.0 is
presently not supported in FreeTDS).

b) or cast the column to a datatype with the requisit features that TDS
4.2 understands, namely the TEXT datatype.

To do this you may either use the convert() function such as "SELECT
 mycol = convert(mycol, text) FROM mytable" or the newer CAST syntax,
"SELECT CAST(mycol as TEXT) FROM mytable"

------------

A second related problem is that some people are having text problems
under TDS 7.0, but no seems to care enough to give an example so it be
fixed. And that is where the CAST(mytextcol as varchar(8000)) would come
in.

Brian

On Sun, 3 Jun 2001, James K. Lowden wrote:

> Paul Schaap wrote:
> 
> > > I saw that freeTDS cannot read Strings from the (SQLServer7)database 
> > > with
> > > a length bigger than 255. freeTDS simply cuts the String.
> > > This happens with datatypes char, varchar, nvarchar, nchar. Any Ideas?
> >
> > Cast them as type "TEXT".
> >
> > P.S. the FAQ is confused/ing here !
> 
> Confused, methinks.
> 
> Are you referring to http://216.254.83.209/freetds/html/x515.htm?
> 
> "The best current solution is convert long strings to varchar(8000) in your 
> query
> text with CAST( variable_name as varchar(2000) ) as variable_name."
> 
> Not so?  Should that read "CAST(variable_name as text) as variable_name"?  
> Or does it
> depend on what client-server-version combination you're using?  Tell the 
> truth,
> please.  No point in updating one lie with another.
> 
> You see, if the fellow who wrote the documentation actually knew what he 
> was talking
> about, this sort of silliness wouldn't happen.  Other kinds of silliness, 
> maybe, but
> not this kind.
> 
> Thanks, Paul.
> 
> --jkl
>