Skip to Content.
Sympa Menu

freetds - RE: [freetds] TEXT/NTEXT header format question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] TEXT/NTEXT header format question
  • Date: Thu, 5 Feb 2004 12:12:01 -0000

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





Archive powered by MHonArc 2.6.24.

Top of Page