FreeTDS Development Group

[<lists-tds AT mymailfilter.net>]

Thanks, that certainly explains the codes with which I was struggling.

> It's an RPC with parameters: -1 (output), NULL ntext, sql text, 1...
> It seem an sp_prepare call... I don't understand where this code came
> cause is not very good. A better way is to use RPC, withot sp_prepare...

The information I sent was from a packet taken from the wire going from an 
ADO-based client toward an MSSQL 2000 server.  The underlying ADO library 
or db-lib would've generated the TDS packet.

I'm trying to interpret the packet more than I'm trying to construct it 
right now, and based on the current information it doesn't seem to be very 
straight forward -- without knowing how many parameters, I'm not sure how 
to tell where the parameters end and the SQL text begins.  

For example,

0030  fb 04 84 b8 00 00 03 01 00 88 00 00 01 00 21 00   ..............!.
0040  5b 00 6d 00 6b 00 31 00 5d 00 2e 00 2e 00 73 00   [.m.k.1.].....s.
0050  70 00 5f 00 70 00 72 00 6f 00 63 00 65 00 64 00   p._.p.r.o.c.e.d.
0060  75 00 72 00 65 00 5f 00 70 00 61 00 72 00 61 00   u.r.e._.p.a.r.a.
0070  6d 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00   m.s._.r.o.w.s.e.
0080  74 00 00 00 00 00 e7 1e 00 1e 00 6d 00 6b 00 5f   t..........m.k._
0090  00 66 00 69 00 6e 00 64 00 5f 00 70 00 72 00 6f   .f.i.n.d._.p.r.o
00a0  00 64 00 75 00 63 00 74 00 00 00 38 01 00 00 00   .d.u.c.t...8....
00b0  00 00 e7 00 00 ff ff 00 00 e7 00 00 ff ff         ..............

0030                                            21 00                 !.

If 21 (1st byte after the tds header) is interpretted as the length of the 
procedure name (33), Length(sp_procedure_params_rowset) != 33.

FYI:
---
[mk1] is the database name.
mk_find_product is the procedure name being called.

This packet is some sort of "prepare" activity being generated by the ADO 
driver, as the procedure would not be called like this.

Here's an sp_unprepare the supports your explanation of the first byte 
following the header being the stored procedure length:

0000  00 01 03 e7 d7 aa 00 10 a4 93 e9 87 08 00 45 00   ..............E.
0010  00 53 b8 98 40 00 80 06 ab 5b c0 a8 0a c8 c0 a8   .S..@....[......
0020  0a 98 10 53 05 99 c6 fe 83 29 42 2a 18 20 50 18   ...S.....)B*. P.
0030  fb 04 07 5b 00 00 03 01 00 2b 00 00 01 00 0c 00   ...[.....+......
0040  73 00 70 00 5f 00 75 00 6e 00 70 00 72 00 65 00   s.p._.u.n.p.r.e.
0050  70 00 61 00 72 00 65 00 00 00 00 00 38 01 00 00   p.a.r.e.....8...
0060  00                                                .

But here's one that seems to pack a couple of procedures together:

0030  fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00   ..=.............
0040  73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00   s.p._.p.r.e.p.a.
0050  72 00 65 00 00 00 00 01 26 04 00 00 00 63 5c 00   r.e.....&....c\.
0060  00 00 5c 00 00 00 40 00 50 00 31 00 20 00 69 00   ..\...@.P.1. .i.
0070  6e 00 74 00 20 00 4f 00 55 00 54 00 50 00 55 00   n.t. .O.U.T.P.U.
0080  54 00 2c 00 40 00 50 00 32 00 20 00 76 00 61 00   T.,.@.P.2. .v.a.
0090  72 00 63 00 68 00 61 00 72 00 28 00 36 00 34 00   r.c.h.a.r.(.6.4.
00a0  29 00 2c 00 40 00 50 00 33 00 20 00 76 00 61 00   ).,.@.P.3. .v.a.
00b0  72 00 63 00 68 00 61 00 72 00 28 00 31 00 32 00   r.c.h.a.r.(.1.2.
00c0  29 00 00 00 63 46 00 00 00 46 00 00 00 20 00 45   )...cF...F... .E
00d0  00 58 00 45 00 43 00 20 00 40 00 50 00 31 00 3d   .X.E.C. .@.P.1.=
00e0  00 6d 00 6b 00 5f 00 66 00 69 00 6e 00 64 00 5f   .m.k._.f.i.n.d._
00f0  00 70 00 72 00 6f 00 64 00 75 00 63 00 74 00 20   .p.r.o.d.u.c.t. 
0100  00 40 00 50 00 32 00 2c 00 40 00 50 00 33 00 20   .@.P.2.,.@.P.3. 
0110  00 20 00 00 00 38 01 00 00 00                     . ...8....

I think one can follow your explanation through the bytes and see the 
meaning of some of the parameters and such, but how does one break it 
down and dynamically determine what's there?  In the second data chunk 
above, the sp_prepare starts 1 byte after the length:

0030  fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00   ..=.............
                                                ^^
0040  73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00   s.p._.p.r.e.p.a.
      ^^

Length = 10, a 00 byte and the start of a procedure that has a length of 
10.  However, my original data held the parameter info between the length 
byte and the actual SQL text:

> 0030  f7 48 ca 56 00 00 03 01 00 fa 00 00 01 00 ff ff   .H.V............
                                                  ^^
> 0040  0b 00 00 00 00 01 26 04 04 ff ff ff ff 00 00 63   ......&........c
> 0050  00 00 00 00 09 04 00 01 32 ff ff ff ff 00 00 63   ........2......c
> 0060  ba 00 00 00 09 04 00 01 32 ba 00 00 00 64 00 65   ........2....d.e
                                               ^^ 
> 0070  00 63 00 6c 00 61 00 72 00 65 00 20 00 40 00 70   .c.l.a.r.e. .@.p

What about the data tells me where the parameters start/end, and where the 
SQL text starts/ends, etc?  I think I need to keep looking at this, I must 
be missing something...

Thanks again for the info.


On 21 Jul 2003, Frediano Ziglio wrote:

> I didn't read all messages related so perhaps some information will be
> duplicated.
> >  
> > 0030  f7 48 ca 56 00 00
> 
> This should be a TCP/IP info or other related.
> >  03 01 00 fa 00 00 01 00
> 
> Header: type 3 (RPC) final, length 0xfa (correct)
> 
> >  ff ff   .H.V............
> 
> Length of store procedure name. TDS8 (mssql2k+, not mssql7) handle null
> name (-1 length) using numbers instead of names (I know only some
> numbers like 15 -> sp_unprepare).
> 
> > 0040  0b 00 
> 
> This is RPC numbers... perhaps sp_executesql
> 
> > 00 00 
> 
> flags
> 
> > 00 01 26 04 04 ff ff ff ff
> 
> output parameter, type INTN (0x26) length 4 (data and type length),
> value -1
> 
> >  00 00 63   ......&........c
> 
> input parameter, NTEXT
> 
> > 0050  00 00 00 00 
> 
> ??? strange, must be length of type... perhaps I have to check tds
> code...
> 
> > 09 04 00 01 32
> 
> collation info (0x409 = english)
> 
> >  ff ff ff ff
> 
> length of text (NULL)
> 
> >  00 00 63   ........2......c
> 
> input NTEXT...
> 
> > 0060  ba 00 00 00
> 
> yes, this is length 
> 
> >  09 04 00 01 32 
> 
> collate
> 
> > ba 00 00 00
> 
> length 
> 
> >  64 00 65   ........2....d.e
> > 0070  00 63 00 6c 00 61 00 72 00 65 00 20 00 40 00 70   .c.l.a.r.e. .@.p
> > 0080  00 34 00 20 00 69 00 6e 00 74 00 65 00 67 00 65   .4. .i.n.t.e.g.e
> > 0090  00 72 00 0d 00 0a 00 65 00 78 00 65 00 63 00 20   .r.....e.x.e.c. 
> > 00a0  00 6a 00 64 00 5f 00 74 00 65 00 73 00 74 00 5f   .j.d._.t.e.s.t._
> > 00b0  00 30 00 30 00 31 00 20 00 40 00 70 00 31 00 3d   .0.0.1. .@.p.1.=
> > 00c0  00 33 00 2c 00 20 00 40 00 70 00 32 00 3d 00 27   .3.,. .@.p.2.=.'
> > 00d0  00 74 00 65 00 73 00 74 00 27 00 2c 00 20 00 40   .t.e.s.t.'.,. .@
> > 00e0  00 70 00 33 00 3d 00 20 00 27 00 30 00 37 00 2f   .p.3.=. .'.0.7./
> > 00f0  00 31 00 37 00 2f 00 32 00 30 00 30 00 33 00 27   .1.7./.2.0.0.3.'
> > 0100  00 2c 00 20 00 40 00 40 00 70 00 34 00 3d 00 40   .,. .@.@.p.4.=.@
> > 0110  00 70 00 34 00 20 00 6f 00 75 00 74 00 70 00 75   .p.4. .o.u.t.p.u
> > 0120  00 74 00 0d 00 0a 00
> 
> just sql code you send
> 
> >  00 00 26 04 04 01 00 00 00   .t.......&......
> 
> input type INTN, length 4, value 1
> 
> > 
> > The actual SQL code is:
> > 
> > declare @p4 integer
> > exec jd_test_001 @p1=3, @p2='test', @p3= '07/17/2003', @@p4=@p4 output
> > 
> > I'm confused about what appears to be some flags and info preceding the 
> > actual SQL code.
> 
> ...
> 
> Well, any byte is decoded...
> 
> It's an RPC with parameters: -1 (output), NULL ntext, sql text, 1...
> It seem an sp_prepare call... I don't understand where this code came
> cause is not very good. A better way is to use RPC, withot sp_prepare...
> 
> I've learn two things:
> - length of NTEXT is before collation information
> - if parameter list is empty I can use a NULL value per parameters
> 
> freddy77
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>