FreeTDS Development Group

["Paul Schaap" <paul.schaap AT dingoblue.com.au>]

Hi,

> 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"
> 
> ------------

Perfect explanation ! Love it.

> 
> 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.

I'd help but I'm big endian and stuck at 4.2 ;-), but I spose you can
spank me for not helping getting 7.0 working ....


> 
> 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.

No probs.

> > 
> > --jkl
> >

CHEERS
Paul