FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

David,

D'oh! after a couple hours figuring this out, the solution turns out to be
amazingly prosaic.

You've hit a TDS packet header.

with that amount of data of course, your row data will span multiple TDS
packets (default size 4096 on SQL 2K)

The solution became obvious when I used freetds to retrieve a like row from
a like table I set up.
In my TDS dump I saw the following. Here's a snippet...The repeated 'B's are
my NTEXT column data....


01a0  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
01b0  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
01c0  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
01d0  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
01e0  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
01f0  00 42 00 42 00 42 00 42                            |.B.B.B.B|


Received header @ 2004-02-05 12:04:22.829931
0000  04 00 02 00 03 9f 0a 00                            |........|


Received packet @ 2004-02-05 12:04:22.830500
0000  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
0010  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
0020  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
0030  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|
0040  00 42 00 42 00 42 00 42  00 42 00 42 00 42 00 42   |.B.B.B.B .B.B.B.B|


So, you're not using FreeTDS to do this job (whatever it is)  for you. 
The question springs to mind, why not ?

regards,

Bill




> -----Original Message-----
> From: David Eastman [SMTP:deastman AT email.com]
> Sent: 04 February 2004 22:17
> To:   freetds AT lists.ibiblio.org
> Subject:      [freetds] TEXT/NTEXT header format question
> 
> Some hex extracts are below.  This is happening on various queries against
> a SQL 2000 server.  I was beginning to think it was happening on the 8k
> boundary since the first example I was debugging was in a 9k text field,
> but the example below is only about 4k.  Note that the length field in the
> column header is off by 8 bytes, and there are an extraneous 8 bytes in
> the middle of the text.  This happens to just about any result of
> reasonable size that has text in it, at random locations as far as I can
> tell.
> 
> RESULTS Message (0x81 is at 003E, first row starts at 0274):
> 0000: 00 07 E9 CD 91 FB 00 06 5B 38 18 72 08 00 45 00 0010: 05 DC 11 44 40
> 00 80 06 1C 2F CF 89 94 7D CF 89 | . . . . . . . . [ 8 . r . . E . . . . D
> @ . . . . / . . . } . .
> 0020: 94 18 05 99 0B 87 2A 13 2E C8 93 CC 0D 60 50 10 0030: FF FF F7 2E 00
> 00 04 00 10 00 00 3E 01 00 81 0E | . . . . . . * . . . . . . ` P . . . . .
> . . . . . . . > . . . .
> 0040: 00 00 00 08 00 38 0A 64 00 69 00 64 00 5F 00 73 0050: 00 79 00 6F 00
> 5F 00 69 00 64 00 00 00 09 00 26 | . . . . . 8 . d . i . d . _ . s . y . o
> . _ . i . d . . . . . &
> 0060: 04 0E 64 00 69 00 64 00 5F 00 64 00 63 00 69 00 0070: 5F 00 73 00 79
> 00 6F 00 5F 00 69 00 64 00 00 00 | . . d . i . d . _ . d . c . i . _ . s .
> y . o . _ . i . d . . .
> 0080: 09 00 26 04 14 64 00 69 00 64 00 5F 00 64 00 6F 0090: 00 66 00 5F 00
> 73 00 79 00 6F 00 5F 00 69 00 64 | . . & . . d . i . d . _ . d . o . f . _
> . s . y . o . _ . i . d
> 00A0: 00 5F 00 69 00 6E 00 70 00 75 00 74 00 00 00 09 00B0: 00 26 04 15 64
> 00 69 00 64 00 5F 00 64 00 6F 00 | . _ . i . n . p . u . t . . . . . & . .
> d . i . d . _ . d . o .
> 00C0: 66 00 5F 00 73 00 79 00 6F 00 5F 00 69 00 64 0000D0: 5F 00 6F 00 75
> 00 74 00 70 00 75 00 74 00 00 00 | f . _ . s . y . o . _ . i . d . _ . o .
> u . t . p . u . t . . .
> 00E0: 09 00 6F 08 0C 64 00 69 00 64 00 5F 00 65 00 66 00F0: 00 66 00 5F 00
> 64 00 61 00 74 00 65 00 00 00 09 | . . o . . d . i . d . _ . e . f . f . _
> . d . a . t . e . . . .
> 0100: 00 6F 08 0D 64 00 69 00 64 00 5F 00 74 00 65 00 0110: 72 00 6D 00 5F
> 00 64 00 61 00 74 00 65 00 00 00 | . o . . d . i . d . _ . t . e . r . m .
> _ . d . a . t . e . . .
> 0120: 08 00 E7 64 00 09 04 D0 00 34 08 64 00 69 00 64 0130: 00 5F 00 6E 00
> 61 00 6D 00 65 00 00 00 09 00 E7 | . . . d . . . . . 4 . d . i . d . _ . n
> . a . m . e . . . . . .
> 0140: FE 01 09 04 D0 00 34 0F 64 00 69 00 64 00 5F 000150: 64 00 65 00 73
> 00 63 00 72 00 69 00 70 00 74 00 | . . . . . . 4 . d . i . d . _ . d . e .
> s . c . r . i . p . t .
> 0160: 69 00 6F 00 6E 00 00 00 08 00 34 0C 64 00 69 00 0170: 64 00 5F 00 63
> 00 6D 00 64 00 5F 00 74 00 79 00 | i . o . n . . . . . 4 . d . i . d . _ .
> c . m . d . _ . t . y .
> 0180: 70 00 65 00 00 00 09 00 63 00 FC 00 00 09 04 D0 0190: 00 34 12 00 64
> 00 63 00 69 00 5F 00 69 00 6E 00 | p . e . . . . . c . . . . . . . . 4 . .
> d . c . i . _ . i . n .
> 01A0: 74 00 65 00 72 00 66 00 61 00 63 00 65 00 5F 00 01B0: 64 00 65 00 66
> 00 73 00 12 64 00 69 00 64 00 5F | t . e . r . f . a . c . e . _ . d . e .
> f . s . . d . i . d . _
> 01C0: 00 65 00 78 00 74 00 65 00 6E 00 73 00 69 00 6F 01D0: 00 6E 00 5F 00
> 64 00 61 00 74 00 61 00 00 00 08 | . e . x . t . e . n . s . i . o . n . _
> . d . a . t . a . . . .
> 01E0: 00 E7 28 00 09 04 D0 00 34 0E 64 00 69 00 64 00 01F0: 5F 00 63 00 72
> 00 65 00 61 00 74 00 65 00 64 00 | . . ( . . . . . 4 . d . i . d . _ . c .
> r . e . a . t . e . d .
> 0200: 5F 00 69 00 64 00 00 00 08 00 3D 0E 64 00 69 00 0210: 64 00 5F 00 63
> 00 72 00 65 00 61 00 74 00 65 00 | _ . i . d . . . . . = . d . i . d . _ .
> c . r . e . a . t . e .
> 0220: 64 00 5F 00 64 00 74 00 00 00 09 00 E7 28 00 09 0230: 04 D0 00 34 0E
> 64 00 69 00 64 00 5F 00 75 00 70 | d . _ . d . t . . . . . . ( . . . . . 4
> . d . i . d . _ . u . p
> 0240: 00 64 00 61 00 74 00 65 00 64 00 5F 00 69 00 64 0250: 00 00 00 09 00
> 6F 08 0E 64 00 69 00 64 00 5F 00 | . d . a . t . e . d . _ . i . d . . . .
> . o . . d . i . d . _ .
> 0260: 75 00 70 00 64 00 61 00 74 00 65 00 64 00 5F 00 0270: 64 00 74 00 D1
> E8 03 00 00 04 C9 00 00 00 04 F3 | u . p . d . a . t . e . d . _ . d . t .
> . . . . . . . . . . . .
> 
> RESULTROW extract, beginning of the problem text column (30th row of the
> response):
> 0000: D1 BF 81 03 00 04 D0 A8 09 00 04 95 87 03 00 04 0010: BE 81 03 00 08
> F1 91 00 00 D0 A5 01 01 08 7F 24 | . . . . . . . . . . . . . . . . . . . .
> . . . . . . . . . . . $
> 0020: 2D 00 00 00 00 00 24 00 47 00 65 00 74 00 20 00 0030: 41 00 6C 00 6C
> 00 20 00 73 00 65 00 63 00 75 00 | - . . . . . $ . G . e . t .   . A . l .
> l .   . s . e . c . u .
> 0040: 72 00 69 00 74 00 69 00 65 00 73 00 FF FF 01 00 0050: 10 FD FF 1A C8
> 07 00 00 00 98 08 00 00 01 00 05 | r . i . t . i . e . s . . . . . . . . .
> . . . . . . . . . . . .
> 0060: 00 64 75 6D 6D 79 54 53 00 EC 0F 00 00 3C 00 49 0070: 00 6E 00 74 00
> 65 00 72 00 66 00 61 00 63 00 65 | . d u m m y T S . . . . . < . I . n . t
> . e . r . f . a . c . e
> 
> In the middle of the text data (note that after the 'sym_syc_id' text
> there are 8 bytes that aren't unicode text - 04 00 10 00 00 3E 13 00):
> 0D80: 00 6D 00 62 00 6F 00 6C 00 5F 00 63 00 61 00 74 0D90: 00 65 00 67 00
> 6F 00 72 00 79 00 20 00 6F 00 6E | . m . b . o . l . _ . c . a . t . e . g
> . o . r . y .   . o . n
> 0DA0: 00 20 00 73 00 79 00 6D 00 5F 00 73 00 79 00 63 0DB0: 00 5F 00 69 00
> 64 04 00 10 00 00 3E 14 00 00 3D | .   . s . y . m . _ . s . y . c . _ . i
> . d . . . . . > . . . =
> 0DC0: 00 73 00 79 00 63 00 5F 00 69 00 64 00 0D 00 0A 0DD0: 00 6C 00 65 00
> 66 00 74 00 20 00 6A 00 6F 00 69 | . s . y . c . _ . i . d . . . . . l . e
> . f . t .   . j . o . i
> 
> > David,
> >
> > If you can send a couple example of the ROW message (0xd1)  you are
> trying
> > to deconstruct, I'll take a look for you.
> > The preceding RESULTS message (0x81) would be useful as well. 
> > 
> > Just a hex representation of this data will be fine. 
> > 
> > I presume you're talking SQL 2000 (TDS 7/8)
> > 
> > Bill
> 
> > -----Original Message-----
> > From:       David Eastman [SMTP:deastman at email.com]
> > Sent:       03 February 2004 21:38
> > To: freetds at lists.ibiblio.org
> > Subject:    [freetds] TEXT/NTEXT header format question
> > 
> > I've got a question I'm hoping someone here can help me with... I'm
> > trying to parse TDS packets, and I'm having problems with TEXT/NTEXT
> > columns.  From the TDS source I've determined that bytes 25-28 of the
> > column header are the length of the data, starting at byte 29. 
> > However, in certain queries, I'm finding that this length is off by a
> > few bytes, which of course not only corrupts my column data, but
> > means that I'm not picking up the next column correctly.  I don't see
> > anything in the TDS source to deal with this, and I've confirmed from
> > some network captures that it is simply a case of the length bytes
> > mentioned above not matching the actual text length, so obviously
> > there must be some kind of offset or other information I'm missing. 
> > Does anyone have a clue?  Is there a more up-to-date version of the
> > protocol document that describes the column header for text columns?
> 
> -- 
> ___________________________________________________________
> Sign-up for Ads Free at Mail.com
> http://promo.mail.com/adsfreejump.htm
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds