Debian Codegraph @

doc/tds.html

raw ◀ ▶
1 Debian patch touch this file
1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
2 "http://www.w3.org/TR/html4/strict.dtd">
3 <html>
4 <head>
5 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
6 <title>TDS Protocol Documentation</title>
7 </head>
8
9 <body>
10 <H1>TDS Protocol Documentation</h1>
11 <P>
12 This document attempts to cover the TDS protocol for:
13 </P>
14
15 <table border=1 summary="protocol.versions">
16 <tr><td align=center><b>TDS Version</b></td><td><b>Supported Products</b></td></tr>
17 <tr><td align=center>4.2</td><td>Sybase SQL Server &lt; 10 and Microsoft SQL Server 6.5</td></tr>
18 <tr><td align=center>5.0</td><td>Sybase SQL Server &gt;= 10</td></tr>
19 <tr><td align=center>7.0</td><td>Microsoft SQL Server 7.0</td></tr>
20 <tr><td align=center>7.1</td><td>Microsoft SQL Server 2000</td></tr>
21 <tr><td align=center>7.2</td><td>Microsoft SQL Server 2005</td></tr>
22 </table>
23
24 <h2>Contents</h2>
25
26 <ul>
27 <li><a href="#terms">Common Terms</a>
28 <li><a href="#examples">Typical Usage Sequences</a>
29 <li><a href="#packet">The Packet Format</a>
30 <li><a href="#login">Login Packet</a>
31 <li><a href="#login7">TDS 7.0 Login Packet</a>
32 <li><a href="#collate">Collation structure</a>
33 <li><a href="#requests">Client requests</a>
34 <li><a href="#responses">Server Responses</a>
35 <li><a href="http://jtds.sourceforge.net/apiCursors.html">OCBC stored procedures (by jtds)</a>
36 </ul>
37
38 <h2 class="section">Common Terms</h2>
39 <p>
40 <a name="terms"></a>
41 <pre>
42 TDS protocol versions
43 TDS 5.0 tds version 5.0
44 TDS 7.0 tds version 7.0
45 TDS 7.0+ tds version 7.0, 7.1 and 7.2
46 TDS 5.0- tds version 5.0 and previous
47
48 Variable types used in this document:
49 CHAR 8-bit char
50 CHAR[6] string of 6 chars
51 CHAR[n] variable length string
52 XCHAR single byte (TDS 5.0-) or ucs2le (TDS 7.0+) characters
53 INT8 8-bit int
54 INT16 16-bit int
55 INT32 32-bit int
56 UCS2LE Unicode in UCS2LE format
57 </pre>
58 <p>Note: FreeTDS uses TDS_TINYINT for INT8 and TDS_SMALLINT for INT16.</p>
59
60 <h2 class="section">Typical Usage sequences</h2>
61
62 <p><a name="examples"></a>
63 These are TDS 4.2 and not meant to be 100% correct, but
64 I thought they might be helpful to get an overall view
65 of what goes on.
66 <pre>
67 --&gt; Login
68 &lt;-- Login acknowledgement
69
70 --&gt; INSERT SQL statement
71 &lt;-- Result Set Done
72
73 --&gt; SELECT SQL statement
74 &lt;-- Column Names
75 &lt;-- Column Info
76 &lt;-- Row Result
77 &lt;-- Row Result
78 &lt;-- Result Set Done
79
80 --&gt; call stored procedure
81 &lt;-- Column Names
82 &lt;-- Column Info
83 &lt;-- Row Result
84 &lt;-- Row Result
85 &lt;-- Done Inside Process
86 &lt;-- Column Names
87 &lt;-- Column Info
88 &lt;-- Row Result
89 &lt;-- Row Result
90 &lt;-- Done Inside Process
91 &lt;-- Return Status
92 &lt;-- Process Done
93 </pre>
94
95 <h2 class="section">The packet format</h2>
96 <p>
97 <a name="packet"></a>
98 Every information in TDS protocol (query, RPCs, responses and so on) is splitted in packets.</p>
99 <p>All packets start with the following 8 byte header.
100
101 <pre>
102 INT8 INT8 INT16 4 bytes
103 +----------+-------------+----------+--------------------+
104 | packet | last packet | packet | unknown |
105 | type | indicator | size | |
106 +----------+-------------+----------+--------------------+
107
108 Fields:
109 packet type
110 0x01 <a href="#p1">TDS 4.2 or 7.0 query</a>
111 0x02 <a href="#login">TDS 4.2 or 5.0 login packet</a>
112 0x03 <a href="#p3">RPC</a>
113 0x04 <a href="#responses">responses from server</a>
114 0x06 cancels
115 0x07 Used in <a href="#p7">Bulk Copy</a>
116 0x0F TDS 5.0 query
117 0x10 <a href="#login7">TDS 7.0 login packet</a>
118 0x11 <a href="#auth7">TDS 7.0 authentication packet</a>
119 0x12 TDS 8 prelogin packet
120 last packet indicator
121 0x00 if more packets
122 0x01 if last packet
123 packet size
124 (in network byte order)
125 unknown?
126 always 0x00
127 this has something to do with server to server communication/rpc stuff
128 </pre>
129
130 <p>The remainder of the packet depends on the type of information it is
131 providing. As noted above, packets break down into the types query, login,
132 response, and cancels. Response packets are further split into multiple
133 sub-types denoted by the first byte (a.k.a. the token) following the
134 above header.</p>
135
136 <p><i>Note:</i>
137 A TDS packet that is longer than 512 bytes is split on the 512 byte
138 boundary and the "more packets" bit is set. The full TDS packet is reassembled
139 from its component 512 byte packets with the 8-byte headers stripped out.
140 512 is the block_size in the login packet, so it could be set to a
141 different values.
142 In Sybase you can configure a range of valid block sizes.
143 TDS 7.0+ use a default of 4096 as block size.</p>
144 <hr>
145 <p>
146 <a name="login"></a>
147 <h2 class="section">TDS 4.2 &amp; 5.0 Login Packet</h2>
148
149 <p>
150 Packet type (first byte) is 2. The numbers
151 on the left are decimal offsets <i>including</i> the
152 8 byte packet header.
153 <pre>
154 byte var type description
155 ------------------------------
156 8 CHAR[30] host_name
157 38 INT8 host_name_length
158 39 CHAR[30] user_name
159 69 INT8 user_name_length
160 70 CHAR[30] password
161 100 INT8 password_length
162 101 CHAR[30] host_process
163 131 INT8 host_process_length
164 132 ? magic1[6] /* mystery stuff */
165 138 INT8 bulk_copy
166 139 ? magic2[9] /* mystery stuff */
167 148 CHAR[30] app_name
168 178 INT8 app_name_length
169 179 CHAR[30] server_name
170 209 INT8 server_name_length
171 210 ? magic3[1] /* 0, don't know this one either */
172 211 INT8 password2_length
173 212 CHAR[30] password2
174 242 CHAR[223] magic4
175 465 INT8 password2_length_plus2
176 466 INT16 major_version /* TDS version */
177 468 INT16 minor_version /* TDS version */
178 470 CHAR library_name[10] /* "Ct-Library" or "DB-Library" */
179 480 INT8 library_length
180 481 INT16 major_version2 /* program version */
181 483 INT16 minor_version2 /* program version */
182 485 ? magic6[3] /* ? last two octets are 13 and 17 */
183 /* bdw reports last two as 12 and 16 here */
184 /* possibly a bitset flag */
185 488 CHAR[30] language /* e.g. "us-english" */
186 518 INT8 language_length
187 519 ? magic7[1] /* mystery stuff */
188 520 INT16 old_secure /* explanation? */
189 522 INT8 encrypted /* 1 means encrypted all password fields blank */
190 523 ? magic8[1] /* no clue... zeros */
191 524 CHAR sec_spare[9] /* explanation? */
192 533 CHAR[30] char_set /* e.g. "iso_1" */
193 563 INT8 char_set_length
194 564 INT8 magic9[1] /* 1 */
195 565 CHAR[6] block_size /* in text */
196 571 INT8 block_size_length
197 572 ? magic10[25] /* lots of stuff here...no clue */
198 </pre>
199 <p>Any help with the magic numbers would be most appreciated.</p>
200
201 <hr>
202 <p>
203 <a name="login7"></a>
204 <h2 class="section">TDS 7.0+ Login Packet</h2>
205 <pre>
206 byte var type description
207 ---------------------------
208 0 INT32 total packet size
209 4 INT8[4] TDS Version
210 0x00000070 7.0
211 0x01000071 7.1
212 0x02000972 7.2 (7.2.9?)
213 8 INT32 packet size (default 4096)
214 12 INT8[4] client program version
215 16 INT32 PID of client
216 20 INT32 connection id (usually 0)
217 24 INT8 option flags 1
218 0x80 enable warning messages if SET LANGUAGE issued
219 0x40 change to initial database must succeed
220 0x20 enable warning messages if USE &lt;database&gt; issued
221 0x10 enable BCP
222 0x08 use ND5000 floating point format (untested)
223 0x04 use VAX floating point format (untested)
224 0x02 use EBCDIC encoding (untested)
225 0x01 use big-endian byte order (untested)
226 25 INT8 option flags 2
227 0x80 enable domain login security
228 0x40 "USER_SERVER - reserved"
229 0x20 user type is "DQ login"
230 0x10 user type is "replication login"
231 0x08 "fCacheConnect"
232 0x04 "fTranBoundary"
233 0x02 client is an ODBC driver
234 0x01 change to initial language must succeed
235 26 INT8 0x04 spawn user instance (TDS 7.2)
236 0x02 XML data type instances are returned as binary XML (TDS 7.2)
237 0x01 password change requested (TDS 7.2)
238 27 INT8 0x01 SQL Type: 0 = use default, 1 = use T-SQL (TDS 7.2)
239 28 INT8[4] time zone (0x88ffffff ???)
240 32 INT8[4] collation information
241 36 INT16 position of client hostname (86)
242 38 INT16 hostname length
243 40 INT16 position of username
244 42 INT16 username length
245 44 INT16 position of password
246 46 INT16 password length
247 48 INT16 position of app name
248 50 INT16 app name length
249 52 INT16 position of server name
250 54 INT16 server name length
251 56 INT16 position of remote server/password pairs
252 58 INT16 remote server/password pairs length
253 60 INT16 position of library name
254 62 INT16 library name length
255 64 INT16 position of language
256 66 INT16 language name (for italian "Italiano", coded UCS2)
257 68 INT16 position of database name
258 70 INT16 database name length
259 72 INT8[6] MAC address of client
260 78 INT16 position of auth portion
261 80 INT16 NT authentication length
262 82 INT16 next position (same as total packet size)
263 84 INT16 0
264 86 UCS2LE[n] hostname
265 UCS2LE[n] username
266 UCS2LE[n] encrypted password
267 UCS2LE[n] app name
268 UCS2LE[n] server name
269 UCS2LE[n] library name
270 UCS2LE[n] language name
271 UCS2LE[n] database name
272 NT Authentication packet
273
274 NT Authentication packet
275 0 CHAR[8] authentication id "NTLMSSP\0"
276 8 INT32 1 message type
277 12 INT32 0xb201 flags
278 16 INT16 domain length
279 18 INT16 domain length
280 20 INT32 domain offset
281 24 INT16 hostname length
282 26 INT16 hostname length
283 28 INT32 hostname offset
284 32 CHAR[n] hostname
285 CHAR[n] domain
286 See documentation on Samba for detail (or search ntlm authentication for IIS)
287
288 For mssql 2005 before hostname (byte 86) you have
289 86 INT16 next position, or
290 position of file name for a database to be
291 attached during the connection process
292 88 INT16 database filename length
293 90 INT16 new password position
294 92 INT16 new password length
295 94 UCS2LE[n] hostname
296 ... (as above)
297 </pre>
298 <p>"current pos" is the starting byte address for a Unicode string within
299 the packet. The length of that Unicode string immediately follows.
300 That implies there are at least 2 more strings that could be defined.
301 (character set??)
302 </p>
303
304 <p>Username and password are empty if domain authentication is used.</p>
305
306 <p>If the client uses an authentication packet, the server replies with an
307 <a href="#t237">Authentication</a> token followed by an <a href="#auth7">Authentication packet</a>.</p>
308
309 <hr>
310 <h2 class="section">TDS 7.0 Authentication Packet</h2>
311 <p>
312 <a name="auth7"></a>
313 </p>
314
315 <pre>
316 varies
317 +------+
318 | auth |
319 +------+
320
321 auth authentication data
322 for NTLM this message 3
323 </pre>
324
325 <p>This packet usually follows <a href="#t237">Authentication</a> token.</p>
326
327 <hr>
328 <h2 class="section">Types</h2>
329 <p>
330 <a name="types"></a>
331 </p>
332 <table border="0" summary="packets">
333 <tr>
334 <th>HEX</th><th>DEC</th><th>type</th><th>protocol</th><th>nullable</th><th>size</th><th>collate</th>
335 </tr>
336 <tr>
337 <td>0x1F</td><td>31</td><td>SYBVOID</td><td>7+</td><td>no</td><td>0</td><td></td>
338 </tr>
339 <tr>
340 <td>0x22</td><td>34</td><td>SYBIMAGE</td><td></td><td>yes</td><td>4</td><td></td>
341 </tr>
342 <tr>
343 <td>0x23</td><td>35</td><td>SYBTEXT</td><td></td><td>yes</td><td>4</td><td>yes</td>
344 </tr>
345 <tr>
346 <td>0x24</td><td>36</td><td>SYBUNIQUE</td><td>7+</td><td>yes</td><td>1</td><td></td>
347 </tr>
348 <tr>
349 <td>0x25</td><td>37</td><td>SYBVARBINARY</td><td></td><td>yes</td><td>1</td><td></td>
350 </tr>
351 <tr>
352 <td>0x26</td><td>38</td><td>SYBINTN</td><td></td><td>yes</td><td>1</td><td></td>
353 </tr>
354 <tr>
355 <td>0x27</td><td>39</td><td>SYBVARCHAR</td><td></td><td>yes</td><td>1</td><td></td>
356 </tr>
357 <tr>
358 <td>0x2D</td><td>45</td><td>SYBBINARY</td><td></td><td>yes</td><td>1</td><td></td>
359 </tr>
360 <tr>
361 <td>0x2F</td><td>47</td><td>SYBCHAR</td><td></td><td>yes</td><td>1</td><td></td>
362 </tr>
363 <tr>
364 <td>0x30</td><td>48</td><td>SYBINT1</td><td></td><td>no</td><td>0</td><td></td>
365 </tr>
366 <tr>
367 <td>0x32</td><td>50</td><td>SYBBIT</td><td></td><td>no</td><td>0</td><td></td>
368 </tr>
369 <tr>
370 <td>0x34</td><td>52</td><td>SYBINT2</td><td></td><td>no</td><td>0</td><td></td>
371 </tr>
372 <tr>
373 <td>0x38</td><td>56</td><td>SYBINT4</td><td></td><td>no</td><td>0</td><td></td>
374 </tr>
375 <tr>
376 <td>0x3A</td><td>58</td><td>SYBDATETIME4</td><td></td><td>no</td><td>0</td><td></td>
377 </tr>
378 <tr>
379 <td>0x3B</td><td>59</td><td>SYBREAL</td><td></td><td>no</td><td>0</td><td></td>
380 </tr>
381 <tr>
382 <td>0x3C</td><td>60</td><td>SYBMONEY</td><td></td><td>no</td><td>0</td><td></td>
383 </tr>
384 <tr>
385 <td>0x3D</td><td>61</td><td>SYBDATETIME</td><td></td><td>no</td><td>0</td><td></td>
386 </tr>
387 <tr>
388 <td>0x3E</td><td>62</td><td>SYBFLT8</td><td></td><td>no</td><td>0</td><td></td>
389 </tr>
390 <tr>
391 <td>0x40</td><td>64</td><td>SYBSINT1</td><td>5</td><td>no</td><td>0</td><td></td>
392 </tr>
393 <tr>
394 <td>0x41</td><td>65</td><td>SYBUINT2</td><td>5</td><td>no</td><td>0</td><td></td>
395 </tr>
396 <tr>
397 <td>0x42</td><td>66</td><td>SYBUINT4</td><td>5</td><td>no</td><td>0</td><td></td>
398 </tr>
399 <tr>
400 <td>0x43</td><td>67</td><td>SYBUINT8</td><td>5</td><td>no</td><td>0</td><td></td>
401 </tr>
402 <tr>
403 <td>0x62</td><td>98</td><td>SYBVARIANT</td><td>7+</td><td>yes</td><td>4</td><td></td>
404 </tr>
405 <tr>
406 <td>0x63</td><td>99</td><td>SYBNTEXT</td><td>7+</td><td>yes</td><td>4</td><td>yes</td>
407 </tr>
408 <tr>
409 <td>0x67</td><td>103</td><td>SYBNVARCHAR</td><td>7+</td><td>yes</td><td>1</td><td></td>
410 </tr>
411 <tr>
412 <td>0x68</td><td>104</td><td>SYBBITN</td><td></td><td>yes</td><td>1</td><td></td>
413 </tr>
414 <tr>
415 <td>0x6A</td><td>106</td><td>SYBDECIMAL</td><td></td><td>yes</td><td>1</td><td></td>
416 </tr>
417 <tr>
418 <td>0x6C</td><td>108</td><td>SYBNUMERIC</td><td></td><td>yes</td><td>1</td><td></td>
419 </tr>
420 <tr>
421 <td>0x6D</td><td>109</td><td>SYBFLTN</td><td></td><td>yes</td><td>1</td><td></td>
422 </tr>
423 <tr>
424 <td>0x6E</td><td>110</td><td>SYBMONEYN</td><td></td><td>yes</td><td>1</td><td></td>
425 </tr>
426 <tr>
427 <td>0x6F</td><td>111</td><td>SYBDATETIMN</td><td></td><td>yes</td><td>1</td><td></td>
428 </tr>
429 <tr>
430 <td>0x7A</td><td>122</td><td>SYBMONEY4</td><td></td><td>no</td><td>0</td><td></td>
431 </tr>
432 <tr>
433 <td>0x7F</td><td>127</td><td>SYBINT8</td><td></td><td>no</td><td>0</td><td></td>
434 </tr>
435 <tr>
436 <td>0xA5</td><td>165</td><td>XSYBVARBINARY</td><td>7+</td><td>yes</td><td>2 *</td><td></td>
437 </tr>
438 <tr>
439 <td>0xA7</td><td>167</td><td>XSYBVARCHAR</td><td>7+</td><td>yes</td><td>2 *</td><td>yes</td>
440 </tr>
441 <tr>
442 <td>0xAD</td><td>173</td><td>XSYBBINARY</td><td>7+</td><td>yes</td><td>2</td><td></td>
443 </tr>
444 <tr>
445 <td>0xAF</td><td>175</td><td>XSYBCHAR</td><td>7+</td><td>yes</td><td>2</td><td>yes</td>
446 </tr>
447 <tr>
448 <td>0xE1</td><td>225</td><td>SYBLONGBINARY</td><td>5</td><td>yes</td><td>4</td><td></td>
449 </tr>
450 <tr>
451 <td>0xE7</td><td>231</td><td>XSYBNVARCHAR</td><td>7+</td><td>yes</td><td>2 *</td><td>yes</td>
452 </tr>
453 <tr>
454 <td>0xEF</td><td>239</td><td>XSYBNCHAR</td><td>7+</td><td>yes</td><td>2</td><td>yes</td>
455 </tr>
456 </table>
457 <hr>
458 <p>* Under TDS 7.2+ these types allow size to be -1, representing varchar(max), varbinary(max) and nvarchar(max).
459 Data representation for them changes: </p>
460 <ul>
461 <li>size is 64 (not 16) bits</li>
462 <li>size of -1 means NULL</li>
463 <li>size of -2 means the size is unknown</li>
464 <li>the data are split in chunks, where each chunk starts with a 32-bit size</li>
465 <li>a chunk with size &lt;= 0 is the terminal chunk</li>
466 </ul>
467 <p>
468 <a name="collate"></a>
469 <h2 class="section">Collation type - TDS 7.1</h2>
470 <p>The collation structure contains information about the character set encoding and comparison method.</p>
471 <pre>
472 INT16 INT16 INT8
473 +----------+--------+------------+
474 | codepage | flags | charset_id |
475 +----------+--------+------------+
476
477 codepage windows codepage (see <a href="http://www.microsoft.com/globaldev/nlsweb/">http://www.microsoft.com/globaldev/nlsweb/</a>)
478 also specified in lcid column of master..syslanguages
479 flags sort flags
480 0x100 binary compare
481 0x080 width insensitive
482 0x040 Katatype insensitive
483 0x020 accent insensitive
484 0x010 case insensitive
485 If binary flag is specified other flags are not present
486 Low nibble of flags is a charset specifier (like chinese dialect)
487 charset_id charset id in master..syscharsets table or zero for no SQL collations
488 </pre>
489
490 <p>Collations names can be obtained from <code>select name from ::fn_helpcollations()</code>
491 query</p>
492
493 <hr>
494 <h2 id="colInfo"><b class="subsection">Column Metadata</b></h2>
495
496 <pre>
497 INT8 XCHAR[n] INT8 INT32 INT8
498 +-------------+--------------+-------+-------+---------+
499 | column name | column name | flags | user | column |
500 | length | | | type | type |
501 +-------------+--------------+-------+-------+---------+
502
503 varies INT8 INT8 INT16 XCHAR[n] INT8 varies
504 +-------------+----------+----------+----------+------------+--------+--------+
505 | column size |precision | scale | t length | table name | locale | locale |
506 | | | | | | length | info |
507 | (optional) |(optional)|(optional)|(optional)| (optional) | (opt) | (opt) |
508 +-------------+----------+----------+----------+------------+--------+--------+
509
510 column name length
511 column name column name in result set, not necessarily db column name
512 flags bit flags
513 0x1 hidden (TDS 5.0)
514 0x2 key
515 0x10 writable
516 0x20 can be NULL
517 0x40 identity
518 user type usertype column from syscolumns
519 <a href="#types">column type</a> column type
520 column size not present for fixed size columns
521 precision present only for SYBDECIMAL and SYBNUMERIC
522 scale present only for SYBDECIMAL and SYBNUMERIC
523 t length present only for SYBTEXT and SYBIMAGE, length of table name
524 table name present only for SYBTEXT and SYBIMAGE
525 locale length length of locale info (in bytes)
526 only for TDS 5.0 results (not for parameters)
527 locale info unknown
528 only for TDS 5.0 results (not for parameters)
529 </pre>
530
531 <hr>
532
533 <h2 id="requests" class="section">Client request</h2>
534
535 <p>Normal tokens (contained in packets 0xF)
536
537 <pre>
538 TODO
539 </pre>
540
541 <p>Special packets</p>
542 <ul>
543 <li>0x1 1 <a href="#p1">Language</a></li>
544 <li>0x3 3 <a href="#p3">RPC</a> TDS 4.6+</li>
545 <li>0x7 7 <a href="#p7">BCP</a> TDS 5.0+</li>
546 </ul>
547
548 <hr>
549 <h2><a name="p1"><b class="section">Language packet (0x1 1)</b></a></h2>
550 <p>
551 This sample packet contain just SQL commands. It's supported by all TDS version
552 (although TDS 5.0 have others token with similar use)
553 <pre>
554 XCHAR[n]
555 +---------+
556 | string |
557 +---------+
558
559 string SQL text
560 </pre>
561
562 <hr>
563 <h2 id="p3"><b class="section">RPC packet (0x3 3)</b></h2>
564
565 <p> Do not confuse an RPC packet with an RPC token. The RPC packet is supported
566 by all version of TDS; the RPC token is supported only by TDS 5.0 (and has
567 different format). This is the oldest (and the only one in mssql) way to call
568 directly an RPC. Sybase also documents it, but as 0xE.
569
570 <pre>
571 INT16 XCHAR[n] INT16
572 +-------------+----------+-------+----------+
573 | name length | rpc name | flags | params |
574 +-------------+----------+-------+----------+
575
576 name length length of RPC name in characters.
577 mssql2k+ support some core RPC using numbers
578 If a number is used instead of name name length is marked as -1
579 (null) and a INT16 is used for the name.
580 0x1 1 sp_cursor
581 0x2 2 sp_cursoropen
582 0x3 3 sp_cursorprepare
583 0x4 4 sp_cursorexecute
584 0x5 5 sp_cursorprepexec
585 0x6 6 sp_cursorunprepare
586 0x7 7 sp_cursorfetch
587 0x8 8 sp_cursoroption
588 0x9 9 sp_cursorclose
589 0xA 10 sp_executesql
590 0xB 11 sp_prepare
591 0xC 12 sp_execute ???
592 0xD 13 sp_prepexec
593 0xE 14 sp_prepexecrpc
594 0xF 15 sp_unprepare
595 sp_execute seems to have some problems, even MS ODBC use name
596 version instead of number.
597 rpc name name of RPC.
598 flags bit flags.
599 0x1 1 recompile procedure (TDS 7.0+/TDS 5.0)
600 0x2 2 no metadata (TDS 7.0+)
601 (I don't know meaning of "no metadata" -- freddy77)
602 params parameters. See below
603 </pre>
604
605 <p>Every parameter has the following structure</p>
606
607 <pre>
608 +-----------+------+
609 | data info | data |
610 +-----------+------+
611 data info data information. See below
612 data data. See results for detail
613 </pre>
614
615 <h4>Data info structure</h4>
616 <pre>
617 INT8 XCHAR[n] INT8 INT32
618 +-------------+------------+-------+--------------------+
619 | name length | param name | flags | usertype (TDS 5.0) |
620 +-------------+------------+-------+--------------------+
621 INT8 varies varies INT8[5] INT8
622 +------+-------+----------+---------------+------------------+
623 | type | size | optional | collate | locale |
624 | | (opt) | (opt) | info(TDS 7.1) | length (TDS 5.0) |
625 +------+-------+----------+---------------+------------------+
626
627 name length parameter name length (0 if unused)
628 param name parameter name
629 flags bit Name Meaning
630 0x1 TDS_RPC_OUTPUT output parameter
631 0x2 TDS_RPC_NODEF output parameter has no default value.
632 Valid only with TDS_RPC_OUTPUT.
633
634 usertype usertype
635 type param type
636 size see <a href="#t129">Results</a>
637 optional see <a href="#t129">Results</a>. Blobs DO NOT have
638 optional on input parameters (output blob parameters
639 are not supported by any version of TDS).
640 collate info only for type that want collate info and using TDS 7.1
641 locale length locale information length. Usually 0 (if not locale
642 information follow, the structure is unknown)
643 </pre>
644
645 <h2>Chained RPCs</h2>
646 <p>Under TDS 7.0+ is possible to chain multiple RPCs together. This is useful to limit packets and round-trips with server. RPCs can be chained using byte 0x80 (TDS 7.0/TDS 7.1) or 0xFF (TDS 7.2).</p>
647
648 <pre>
649 INT8 INT8
650 +-----+------------------------+----------------+-----+
651 | RPC | 0x80 (TDS 7.0/TDS 7.1) | 0xFF (TDS 7.2) | RPC | ...
652 +-----+------------------------+----------------+-----+
653 </pre>
654
655 <hr>
656
657 <h2 id="p7" class="section">Bulk Copy packet (0x7 7)</h2>
658
659 <p>This documents a TDS 5.0 packet. It might be true for others....
660
661 <h3>BCP Packet Structure</h3>
662 <pre>
663 INT8 INT16 INT32
664 +----------+-------------+--------------------+
665 | packet | last packet | packet |
666 | type = 7 | indicator | size |
667 +----------+-------------+--------------------+
668
669 followed by N row buffers,
670 where N is computed by exhausting the packet size
671 </pre>
672 <h3>BCP Packet Row Buffer</h3>
673 <pre>
674 INT16 INT8 INT8 INT16
675 +--------------+----------+----------+--------------+
676 | size | ncols | zero | size (again) |
677 +--------------+----------+----------+--------------+
678
679 followed by column buffers (data), where
680
681 first, the fixed-size datatype columns
682
683 fixed size and count (determined by column definition)
684 +--------------+
685 | data .... | [repeats once for each mandatory column]
686 +--------------+
687
688 then, the variable-size (including nullable) datatype columns
689
690 variable size and count
691 +--------------+
692 | data .... | [repeats ncol times]
693 +--------------+
694
695 followed by two tables (!) to describe the column buffers
696
697 <b>Adjustment Table</b> (optional)
698
699 INT8 INT8
700 +----------+----------+
701 | 1 + ncols| offset | [repeats ncol + 1 times]
702 +----------+----------+
703
704 <b>Offset Table</b> (mandatory)
705
706 INT8 INT8
707 +----------+----------+
708 | 1 + ncols| offset | [repeats ncol + 1 times]
709 +----------+----------+
710 </pre>
711
712 <P>The BCP packet has a slightly different Packet header!? </P>
713
714 <h3>Computation of Offset and Adjustment tables</h3>
715
716 <p>The offset and adjustment tables describe the postion of the first byte of each variable-size column. The first element holds the count of elements in the offset/adjustment table. Thereafer, the offsets are arranged in reverse order: the last element &mdash; which is also the last byte of the row buffer &mdash; holds the offset from the start of the row of the first variable-size column. The next-to-last offset table element holds the starting position of the second variable-size column, and so on.</p>
717
718 <h4>Offset Table Example</h4>
719
720 <ol>
721 <li>5</li>
722 <li>31</li>
723 <li>22</li>
724 <li>21</li>
725 <li>8</li>
726 <li>4</li>
727 </ol>
728
729 <p>The first element is 5 because there are five elements in the list. There are 4 column data buffers with 5 endpoints. The first column's data begins at offset 4. Computations:
730
731 <dl>
732 <dt>column 1</dt>
733 <dd>offset 4</dd>
734 <dd>length: 4 = 8 - 4</dd>
735 <dt>column 2</dt>
736 <dd>offset 8</dd>
737 <dd>length: 13 = 21 - 8</dd>
738 <dt>column 3</dt>
739 <dd>offset 21</dd>
740 <dd>length: 1 = 22 - 21</dd>
741 <dt>column 4</dt>
742 <dd>offset 22</dd>
743 <dd>length: 9 = 31 - 22</dd>
744 </dl>
745
746
747 <p>Any column not accounted for is implicitly NULL. To represent a NULL column between two dataful columns, the offset table will have adjacent entries of the same value. </p>
748
749 <h4>Adjustment table</h4>
750
751 <p>The so-called adjustment table provides for longer rows. The reader will note the Offset table has 8-bit elements, which would limit the width of the table: the last variable column would have to end less than 256 bytes from the start fo the row. Rather than changing the definition of the Offset table, a second table, the Adjustment table, was introduced. It holds high-order bytes for the column offsets. </p>
752
753 <p>In other words, to compute a variable column's offset from the start of the row buffer, the server looks up its offset table value, then consults the same position in the adjustment table, and splices them together. </p>
754
755 <h3>Offset table commentary</h3>
756
757 <p>The BCP packet is very dense. The data formats are governed by the table definition. Non-NULL columns of course must be present; there is no need to count them or compute their size. The NULL columns are undelimited; their boundaries are defined by the minimalist offset table. </p>
758
759 <p>The Adjustment table seems silly at first glance. Why not just make the offset table's elements 16 or or even 32 bits? The reason is overhead. Most rows will have less than 256 bytes of variable column data. By using the adjustment table, the BCP packet avoids adding one or even three empty bytes per column per row. </p>
760
761 <p>Why is the table in reverse order? Because that places the first offset at a known location: the end of each <i>row</i> gives the start of the first column. The server can work its way down the offset table and compute the column sizes. If they don't add up &mdash; if there are data between the (presumed) end of the last column and the start of the offset table &mdash; the server knows it should look for an adjustment table. Because the scheme is infinitely repeatable, rows could one day grow to terabyte widths without redefining the packet structure. </p>
762
763 <hr>
764
765 <h2 class="section">Server Responses</h2>
766 <p>
767 <a name="responses"></a>
768 Responses from the server start with a single octet (token) identifying
769 its type. If variable length, they generally have the length as the second
770 and third bytes
771 <p>
772 Tokens encountered thus far:
773 <table border="0" summary="tokens">
774 <tr>
775 <th>HEX</th><th>DEC</th><th>name</th><th>note</th>
776 </tr>
777 <tr>
778 <td>0x20</td><td>32</td><td><a href="#t32">Param Format 2</a></td><td>5.0 only</td>
779 </tr>
780 <tr>
781 <td>0x21</td><td>33</td><td><a href="#t33">Language</a></td><td>5.0 only, client-side</td>
782 </tr>
783 <tr>
784 <td>0x22</td><td>34</td><td><a href="#t34">OrderBy 2</a></td><td>5.0 only??</td>
785 </tr>
786 <tr>
787 <td>0x61</td><td>97</td><td><a href="#t97">Row Format 2</a></td><td>5.0 only</td>
788 </tr>
789 <tr>
790 <td>0x71</td><td>113</td><td><a href="#t113">"Logout"</a></td><td>5.0? ct_close(), client-side?</td>
791 </tr>
792 <tr>
793 <td>0x79</td><td>121</td><td><a href="#t121">Return Status</a></td><td>&nbsp;</td>
794 </tr>
795 <tr>
796 <td>0x7C</td><td>124</td><td><a href="#t124">Process ID</a></td><td>4.2 only</td>
797 </tr>
798 <tr>
799 <td>0x80</td><td>128</td><td><a href="#t128">Cursor Close</a></td><td>5.0 only</td>
800 </tr>
801 <tr>
802 <td>0x81</td><td>129</td><td><a href="#t129c">Cursor Delete</a></td><td>5.0 only</td>
803 </tr>
804 <tr>
805 <td>0x81</td><td>129</td><td><a href="#t129">7.0 Result</a></td><td>7.0 only</td>
806 </tr>
807 <tr>
808 <td>0x82</td><td>130</td><td><a href="#t130">Cursor Fetch</a></td><td>5.0 only</td>
809 </tr>
810 <tr>
811 <td>0x83</td><td>131</td><td><a href="#t131">Cursor Info</a></td><td>5.0 only</td>
812 </tr>
813 <tr>
814 <td>0x84</td><td>132</td><td><a href="#t132">Cursor Open</a></td><td>5.0 only</td>
815 </tr>
816 <tr>
817 <td>0x86</td><td>134</td><td><a href="#t134">Cursor Declare</a></td><td>5.0 only</td>
818 </tr>
819 <tr>
820 <td>0x88</td><td>136</td><td><a href="#t136">7.0 Compute Result</a></td><td>7.0 only</td>
821 </tr>
822 <tr>
823 <td>0xA0</td><td>160</td><td><a href="#t160">Column Name</a></td><td>4.2 only</td>
824 </tr>
825 <tr>
826 <td>0xA1</td><td>161</td><td><a href="#t161">Column Format</a></td><td>4.2 only</td>
827 </tr>
828 <tr>
829 <td>0xA3</td><td>163</td><td><a href="#t163">Dynamic 2</a></td><td>5.0 only</td>
830 </tr>
831 <tr>
832 <td>0xA4</td><td>164</td><td><a href="#t164">Table names</a></td><td>name of tables in a FOR BROWSE select</td>
833 </tr>
834 <tr>
835 <td>0xA5</td><td>165</td><td><a href="#t165">Column Info</a></td><td>column information in a FOR BROWSE select</td>
836 </tr>
837 <tr>
838 <td>0xA6</td><td>166</td><td><a href="#t166">Option Cmd</a></td><td>5.0 only</td>
839 </tr>
840 <tr>
841 <td>0xA7</td><td>167</td><td><a href="#t167">Compute Names</a></td><td>&nbsp;</td>
842 </tr>
843 <tr>
844 <td>0xA8</td><td>168</td><td><a href="#t168">Compute Result</a></td><td>&nbsp;</td>
845 </tr>
846 <tr>
847 <td>0xA9</td><td>169</td><td><a href="#t169">Order By</a></td><td>&nbsp;</td>
848 </tr>
849 <tr>
850 <td>0xAA</td><td>170</td><td><a href="#t170">Error Message</a></td><td>&nbsp;</td>
851 </tr>
852 <tr>
853 <td>0xAB</td><td>171</td><td><a href="#t171">Info Message</a></td><td>&nbsp;</td>
854 </tr>
855 <tr>
856 <td>0xAC</td><td>172</td><td><a href="#t172">Output Parameters</a></td><td>&nbsp;</td>
857 </tr>
858 <tr>
859 <td>0xAD</td><td>173</td><td><a href="#t173">Login Acknowledgement</a></td><td>&nbsp;</td>
860 </tr>
861 <tr>
862 <td>0xAE</td><td>174</td><td><a href="#t174">Control</a></td><td>&nbsp;</td>
863 </tr>
864 <tr>
865 <td>0xD1</td><td>209</td><td><a href="#t209">Data --- Row Result</a></td><td>&nbsp;</td>
866 </tr>
867 <tr>
868 <td>0xD3</td><td>211</td><td><a href="#t211">Data --- Compute Result</a></td><td>&nbsp;</td>
869 </tr>
870 <tr>
871 <td>0xD7</td><td>215</td><td><a href="#t215">Params</a></td><td>5.0 only</td>
872 </tr>
873 <tr>
874 <td>0xE2</td><td>226</td><td><a href="#t226">Capability</a></td><td>5.0 only. Information on server</td>
875 </tr>
876 <tr>
877 <td>0xE3</td><td>227</td><td><a href="#t227">Environment Change</a></td><td>(database change, packet size, etc...)</td>
878 </tr>
879 <tr>
880 <td>0xE5</td><td>229</td><td><a href="#t229">Extended Error Message</a></td><td>&nbsp;</td>
881 </tr>
882 <tr>
883 <td>0xE6</td><td>230</td><td><a href="#t230">DBRPC</a></td><td>5.0 only RPC calls</td>
884 </tr>
885 <tr>
886 <td>0xE7</td><td>231</td><td><a href="#t231">Dynamic</a></td><td>5.0 only</td>
887 </tr>
888 <tr>
889 <td>0xEC</td><td>236</td><td><a href="#t236">Param Format</a></td><td>5.0 only</td>
890 </tr>
891 <tr>
892 <td>0xED</td><td>237</td><td><a href="#t237">Authentication</a></td><td>7.0 only</td>
893 </tr>
894 <tr>
895 <td>0xEE</td><td>238</td><td><a href="#t238">Result Set</a></td><td>5.0 only</td>
896 </tr>
897 <tr>
898 <td>0xFD</td><td>253</td><td><a href="#t253">Result Set Done</a></td><td>&nbsp;</td>
899 </tr>
900 <tr>
901 <td>0xFE</td><td>254</td><td><a href="#t254">Process Done</a></td><td>&nbsp;</td>
902 </tr>
903 <tr>
904 <td>0xFF</td><td>255</td><td><a href="#t255">Done inside Process</a></td><td>&nbsp;</td>
905 </tr>
906 </table>
907
908 <h2 class="section">Param Format 2 - TDS 5.0 (0x20 32)</h2>
909
910 <p>TODO.
911 <a name="t32">&nbsp;</a> <hr>
912
913 <h2 id="t33" class="section">Language - TDS 5.0 (0x21 33)</h2>
914
915 <pre>
916 INT32 INT8 CHAR[n]
917 +--------+--------+-------+
918 | length | status | query |
919 +--------+--------+-------+
920
921 length total token length
922 status 0 no args
923 1 has args (followed by PARAMFMT/PARAMS)
924 query query (total length - 1)
925 </pre>
926
927 <h2 class="section">Order By 2 (0x22 34)</h2>
928
929 <p>TODO.
930 <a name="t34">&nbsp;</a> <hr>
931
932 <h2 id="t97" class="section">Row Format 2 - TDS 5.0 (0x61 97)</h2>
933
934 <p>TODO.
935
936 <a name="t113">&nbsp;</a> <hr>
937 <h2 class="section">"Logout" (0x71 113)</h2>
938 <p>
939 No information. (1 byte, value=0 ?)
940
941 <a name="t121">&nbsp;</a> <hr>
942 <h2 class="subsection">Return Status (0x79 121)</h2>
943
944 <pre>
945 INT32
946 +---------------+
947 | Return status |
948 +---------------+
949 </pre>
950
951 <p>The return value of a stored procedure.
952 <a name="t124">&nbsp;</a>
953 </p>
954
955 <h2 class="subsection">Process ID (0x7C 124)</h2>
956 <hr>
957
958 <pre>
959 8 bytes
960 +----------------+
961 | process number |
962 +----------------+
963 </pre>
964 <p>Presumably the process ID number for an executing stored procedure.
965 (I'm not sure how this would ever be used by a client. *mjs*)
966
967 <a name="t128">&nbsp;</a> </p>
968
969 <hr>
970 <h2 class="subsection">Cursor Close - TDS 5.0 (0x80 128)</h2>
971
972 <p>TODO.
973
974 <a name="t129c">&nbsp;</a> <hr>
975 <h2 class="subsection">Cursor Delete - TDS 5.0 (0x81 129)</h2>
976
977 <p>TODO.
978
979 <a name="t129">&nbsp;</a> <hr>
980 <h2 class="subsection">Result - TDS 7.0+ (0x81 129)</h2>
981
982 <pre>
983 INT16
984 +----------+-------------+
985 | #columns | column_info |
986 +----------+-------------+
987 </pre>
988
989 <p>The TDS 7.0 column_info is formatted as follows for each column:</p>
990
991 <pre>
992 INT16 INT16 INT8 varies varies INT8[5] INT8 UCS2LE[n]
993 +----------+-------+------+-------+----------+---------------+-------------+---------+
994 | usertype | flags | type | size | optional | collate | name length | name |
995 | | | | (opt) | (opt) | info(TDS 7.1) | | |
996 +----------+-------+------+-------+----------+---------------+-------------+---------+
997
998 usertype type modifier
999 flags bit flags
1000 0x1 can be NULL
1001 0x8 can be written (it's not an expression)
1002 0x10 identity
1003 <a href="#types">type</a> data type, values &gt;128 indicate a large type
1004 size none for fixed size types
1005 4 bytes for blob and text
1006 2 bytes for large types
1007 1 byte for all others
1008 optional
1009 INT8 INT8
1010 +-----------+-------+
1011 numeric/decimal types: | precision | scale |
1012 +-----------+-------+
1013
1014 INT16 UCS2LE[n]
1015 +-------------------+------------+
1016 blob/text types: | table name length | table name |
1017 +-------------------+------------+
1018
1019 collate info are available only using TDS 7.1 and for characters types (but not
1020 for old type like short VARCHAR, only 2byte length versions)
1021 </pre>
1022
1023
1024 <h2 id="t130" class="subsection">Cursor Fetch - TDS 5.0 (0x82 130)</h2>
1025
1026 <p>TODO.
1027
1028 <h2 id="t131" class="subsection">Cursor Info - TDS 5.0 (0x83 131)</h2>
1029
1030 <p>TODO.
1031
1032 <h2 id="t132" class="subsection">Cursor Open - TDS 5.0 (0x84 132)</h2>
1033
1034 <p>TODO.
1035
1036 <h2 id="t134" class="subsection">Cursor Declare - TDS 5.0 (0x86 134)</h2>
1037
1038 <p>TODO.
1039
1040 <h2 id="t136" class="subsection">Compute Result - TDS 7.0+ (0x88 136)</h2>
1041
1042 <p>TODO.
1043
1044 <h2 id="t160" class="subsection">Column Name (0xA0 160)</h2>
1045
1046 <pre>
1047 INT16 INT8 CHAR[n] INT8 CHAR[n]
1048 +--------------+---------+--------------+------+---------+--------------+
1049 | total length | length1 | column1 name | .... | lengthN | columnN name |
1050 +--------------+---------+--------------+------+---------+--------------+
1051 </pre>
1052
1053 <p>This token is the first token that contain result information. Is usually followed by <a href="#t161">Column Format</a> token (0xA1 161)</p>
1054
1055 <hr>
1056 <h2 id="t161" class="subsection">Column Format (0xA1 161)</h2>
1057
1058 <pre>
1059 INT16
1060 +--------------+-------------+
1061 | total length | column_info |
1062 +--------------+-------------+
1063 </pre>
1064
1065 <p>The number of columns is the same of previous <a href="#t160">Column Name</a> token.
1066 <p>The TDS 4.2 column_info is formatted as follows for each column:
1067
1068 <pre>
1069 INT8[4] INT8 varies varies
1070 +-----------+------+-------+----------+
1071 | usertype/ | type | size | optional |
1072 | flags | | (opt) | (opt) |
1073 +-----------+------+-------+----------+
1074
1075 usertype/flags for Sybase
1076
1077 INT32
1078 +----------+
1079 | usertype |
1080 +----------+
1081
1082 usertype/flags for MSSQL
1083
1084 INT16
1085 +----------+-------+
1086 | usertype | flags |
1087 +----------+-------+
1088
1089 usertype type modifier
1090 flags bit flags (only MSSQL)
1091 0x1 can be NULL
1092 0x8 can be written (it's not an expression)
1093 0x10 identity
1094 <a href="#types">type</a> data type
1095 size none for fixed size types
1096 4 bytes for blob and text
1097 1 byte for all others
1098 (TDS 4.2 do not support large types)
1099 optional
1100 INT8 INT8
1101 +-----------+-------+
1102 numeric/decimal types: | precision | scale |
1103 (supported??) +-----------+-------+
1104
1105 INT16 CHAR[n]
1106 +-------------------+------------+
1107 blob/text types: | table name length | table name |
1108 +-------------------+------------+
1109 </pre>
1110
1111 <h2 id="t163" class="subsection">Dynamic 2 - TDS 5.0 (0xA3 163)</h2>
1112
1113 <p>TODO.
1114
1115 <h2 id="t166" class="subsection">Option Cmd - TDS 5.0 (0xA6 166)</h2>
1116
1117 <p>TODO.
1118
1119 <h2 id="t168" class="subsection">Compute Result (0xA8 168)</h2>
1120
1121 <pre>
1122 INT16 INT16 INT8 varies INT8 INT8[n]
1123 +--------------+------------+----------+-------------+---------+-------+
1124 | total length | compute id | #columns | column info | #bycols | bycol |
1125 +--------------+------------+----------+-------------+---------+-------+
1126 </pre>
1127
1128 <p>column info:</p>
1129
1130 <pre>
1131 INT8 INT8 INT32 INT8 varies INT8 varies
1132 +----------+---------+----------+--------+-------+------------------+----------------+
1133 | operator | operand | usertype | column | size | locale length | locale info |
1134 | | | | type | (opt) | info (TDS 5.0) | (TDS 5.0) |
1135 +----------+---------+----------+--------+-------+------------------+----------------+
1136
1137 operator operator
1138 0x4b COUNT
1139 0x4c UNSIGNED? COUNT
1140 0x4d SUM
1141 0x4e UNSIGNED? SUM
1142 0x4f AVG
1143 0x50 UNSIGNED? AVG
1144 0x51 MIN
1145 0x52 MAX
1146 0x09 COUNT_BIG (mssql2k)
1147 0x30 STDEV (mssql2k)
1148 0x31 STDEVP (mssql2k)
1149 0x32 VAR (mssql2k)
1150 0x33 VARP (mssql2k)
1151 0x72 CHECKSUM_AGG (mssql2k)
1152 operand ???
1153 usertype usertype
1154 <a href="#types">column type</a> data type
1155 size data size
1156 locale length length of locale information
1157 locale info locale information (unknown)
1158 </pre>
1159
1160 <p>Each bycol information contains column info for a specific column.</p>
1161 <p>TODO: optional possible?? collate infos ??</p>
1162
1163 <h2 id="t164" class="section">TabName (0xA4 164)</h2>
1164
1165 <p>TDS4/5/7:</p>
1166
1167 <pre>
1168 INT16 INT16 XCHAR[n]
1169 +--------------+-------------+------------+
1170 | total length | name length | table name | ...
1171 +--------------+-------------+------------+
1172
1173 name length table name length
1174 table name table name
1175 </pre>
1176
1177 <p>TDS 7.1:</p>
1178
1179 <pre>
1180 INT16 varies
1181 +--------------+-------------+
1182 | total length | table names |
1183 +--------------+-------------+
1184
1185 table name:
1186 INT8 INT16 XCHAR[n]
1187 +-----------------+------------------+----------------+
1188 | # of components | component length | component name |
1189 +-----------------+------------------+----------------+
1190
1191 ie:
1192 name -&gt; 01 04 00 ucs2le "name"
1193 db..name -&gt; 03 02 00 ucs2le "db" 00 00 04 00 ucs2le "name"
1194 db.dbo.name -&gt; 03 02 00 ucs2le "db" 03 00 ucs2le "dbo" 04 00 ucs2le "name"
1195 </pre>
1196
1197 <h2 id="t165" class="section">Column Info (0xA5 165)</h2>
1198
1199 <pre>
1200 INT16 varies
1201 +--------------+--------------+
1202 | total length | column infos |
1203 +--------------+--------------+
1204 </pre>
1205
1206 <p>column info:</p>
1207
1208 <pre>
1209 INT8 INT8 INT8 INT8 XCHAR[n]
1210 +-------+-------------+-------+-------------+-------------+
1211 | index | table index | flags | name length | column name |
1212 | | | | (opt) | (opt) |
1213 +-------+-------------+-------+-------------+-------------+
1214
1215 index index in result format (1-based)
1216 table index index in previous TabName (1-based)
1217 0 means no table (ie computed)
1218 flags set of flags
1219 0x04 expression
1220 0x08 key
1221 0x10 hidden
1222 0x20 column name present
1223 name length length of following column
1224 column name real column name (result contain the label)
1225 </pre>
1226
1227 <p>This token follow TabName token
1228 <a name="t167">&nbsp;</a>
1229 <a name="t174">&nbsp;</a> </p>
1230 <hr>
1231 <h2 class="section">compute "control" ? (0xA7 167)</h2>
1232 <h2 class="section">"control" (0xAE 174)</h2>
1233 <p>
1234
1235 Miscellaneous note (from *bdw* ?) found with 0xAE: <br>
1236 <pre>
1237 has one byte for each column,
1238 comes between result(238) and first row(209),
1239 I believe computed column info is stored here, need to investigate
1240 </pre>
1241
1242 <h2 id="t169" class="section">Order By (0xA9 169)</h2>
1243
1244 <pre>
1245 INT16 variable (1 byte per col)
1246 +--------+---------+
1247 | length | orders |
1248 +--------+---------+
1249
1250 length Length of packet(and number of cols)
1251 orders one byte per order by indicating the
1252 column # in the output matching the
1253 order from Column Info and Column Names
1254 and data in following Row Data items.
1255 A 0 indicates the column is not in the
1256 resulting rows.
1257
1258 an example:
1259 select first_name, last_name, number from employee
1260 order by salary, number
1261 assuming the columns are returned in the order
1262 queried:
1263 first_name then last_name, then number. we would have:
1264 ----------------
1265 | 2 | 0 | 3 |
1266 ----------------
1267 where length = 2 then the orders evaluate:
1268 0 for salary, meaning there is no salary data returned
1269 3 for number, meaning the 3rd data item corresponding
1270 to a column is the number
1271 </pre>
1272
1273 <p><a name="t170">&nbsp;</a>
1274 <a name="t171"></a>
1275 <a name="t229"></a> </P>
1276 <hr>
1277 <h2 class="section">Error Message (0xAA 170)</h2>
1278 <h2 class="section">Non-error Message (0xAB 171)</h2>
1279 <h2 class="section">Extended Error Message (0xE5 229)</h2>
1280
1281 <pre>
1282 INT16 INT32 INT8 INT8
1283 +--------+------------+-------+-------+
1284 | length | msg number | state | level |
1285 +--------+------------+-------+-------+
1286
1287 INT16 XCHAR[n] INT8 XCHAR[n] INT8 XCHAR[n] INT16 INT32
1288 +----------+---------+----------+---------+----------+---------+-----------------+-----------------+
1289 | m length | message | s length | server | p length | process | line#(TDS 7.1-) | line# (TDS 7.2) |
1290 +----------+---------+----------+---------+----------+---------+-----------------+-----------------+
1291
1292 length Length of packet
1293 msg number SQL message number
1294 state ?
1295 level An error if level &gt; 10, a message if level &lt;= 10
1296 m length Length of message
1297 message Text of error/message
1298 s length Length of server name
1299 server Name of "server" ?
1300 p length Length of process name
1301 process name Stored procedure name, if any
1302 line# Line number of input which generated the message
1303 </pre>
1304
1305 <hr>
1306
1307 <h2 class="section">Output Parameters (0xAC 172)</h2>
1308 <p>
1309 <a name="t172">&nbsp;</a>
1310 Output parameters of a stored procedure.
1311 <pre>
1312 INT16 INT8 XCHAR[n] INT8 INT32 INT8
1313 +--------+----------+---------+-------+----------+----------+------+
1314 | length | c length | colname | flags | usertype | datatype | .... |
1315 +--------+----------+---------+-------+----------+----------+------+
1316
1317 length Length of packet
1318 c length Length of colname
1319 colname Name of column
1320 flags 0x1 Nullable
1321 usertype cf. systypes table in database
1322 <a href="#types">datatype</a> Type of data returned
1323
1324 The trailing information depends on whether the datatype is
1325 a fixed size datatype.
1326 N bytes
1327 +---------+
1328 Datatype of fixed size N | data |
1329 +---------+
1330
1331 INT8 INT8 N bytes
1332 +-------------+---------------+--------+
1333 Otherwise | column size | actual size N | data |
1334 +-------------+---------------+--------+
1335 </pre>
1336
1337 <h2 id="t173" class="section">Login Acknowledgement (0xAD 173)</h2>
1338
1339 <pre>
1340 INT16 INT8 4 bytes INT8 XCHAR[n] 4 bytes
1341 +--------+-------+---------+----------+--------+----------+
1342 | length | ack | version | t length | text | ser_ver |
1343 +--------+-------+---------+----------+--------+----------+
1344
1345 length length of packet
1346 ack 0x01 success 4.2
1347 0x05 success 5.0
1348 0x06 failure 5.0
1349 version TDS version 4 bytes: major.minor.?.?
1350 t length length of text
1351 text server name (ie 'Microsoft SQL Server')
1352 ser_ver Server version
1353 (with strange encoding, differring from TDS version)
1354 </pre>
1355
1356 <P><a name="t209">&nbsp;</a>
1357 <a name="t211">&nbsp;</a>
1358 <hr>
1359 <h2 class="section">Data - Row Result (0xD1 209)</h2>
1360 <h2 class="section">Data - Compute Result (0xD3 211)</h2>
1361
1362 <pre>
1363 INT8 variable size
1364 +----------+--------------------+
1365 | token | row data |
1366 +----------+--------------------+
1367 </pre>
1368 <p>
1369 Row data starts with one byte (decimal 209), for variable length types,
1370 a one byte length field precedes the data, for fixed length records just
1371 the data appears.
1372 <br><i>Note:</i> nullable integers and floats are variable length.
1373 <p>
1374 For example: sp_who
1375 <p>
1376 The first field is spid, a smallint
1377 <br>
1378 The second field is status a char(12), in our example "recv sleep "
1379 <p>
1380 The row would look like this:
1381 <pre>
1382 byte 0 is the token
1383 bytes 1-2 are a smallint in low-endian
1384 byte 3 is the length of the char field
1385 bytes 4-15 is the char field
1386
1387 byte 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
1388 hex D1 01 00 0C 72 65 63 76 20 73 6C 65 65 70 20 20
1389 209 1 0 12 r e c v ' ' s l e e p ' ' ' '
1390 </pre>
1391
1392 <h2 id="t215" class="section">Params - TDS 5.0 (0xD7 215)</h2>
1393 <p>
1394 TODO.
1395
1396 <h2 id="t226" class="section">Capability - TDS 5.0 (0xE2 226)</h2>
1397
1398 <pre>
1399 INT16 variable
1400 +--------+--------------+
1401 | length | capabilities |
1402 +--------+--------------+
1403
1404 length Length of capability string
1405 capabilities Server capabilities? Related to login magic?
1406 </pre>
1407
1408 <h2 id="t227" class="section">Environment change (0xE3 227)</h2>
1409
1410 <pre>
1411 INT16 INT8 INT8 CHAR[n] INT8 CHAR[n]
1412 +--------+----------+-----------+---------+-----------+---------+
1413 | length | env code | t1 length | text1 | t2 length | text2 |
1414 +--------+----------+-----------+---------+-----------+---------+
1415
1416 env code Code for what part of environment changed
1417 0x01 database context
1418 0x02 language
1419 0x03 character set
1420 0x04 packet size
1421 0x05 TDS 7.0+ LCID
1422 0x06 TDS 7.0+ ??? (sort method? sql server encoding?)
1423 0x07 Collation info
1424 text1 Old value
1425 text2 New value
1426
1427 text1 and text2 are text information (coded in ucs2 in TDS 7.0+) except
1428 collation info that's a structure (see collation structure)
1429 </pre>
1430
1431 <h2 id="t230" class="section">DBRPC - TDS 5.0 (0xE6 230)</h2>
1432 <p>
1433 TODO.
1434
1435 <h2 id="t231" class="section">Dynamic - TDS 5.0 (0xE7 231)</h2>
1436 <p>
1437 TODO.
1438
1439 <h2 id="t236" class="section">Param Format - TDS 5.0 (0xEC 236)</h2>
1440 <pre>
1441 INT16 INT16 variable size
1442 +---------+------------+-------------------+
1443 | length | number of | parameter info |
1444 | | parameters | |
1445 +---------+------------+-------------------+
1446
1447 length length of message following this field
1448 number of parameters number of parameter formats following
1449 list of formats I (*bdw*) imagine it uses the column format structure.
1450 </pre>
1451
1452 <h2 id="t237" class="section">Authentication - TDS 7.0 (0xED 237)</h2>
1453 <pre>
1454 INT16 varies
1455 +---------+------+
1456 | length | auth |
1457 +---------+------+
1458
1459 length length of authentication data following this field
1460 auth authentication data
1461 for NTLM this is message 2
1462 </pre>
1463
1464 <p>Client reply with <a href="#auth7">Authentication packet</a>.</p>
1465
1466 <h2 id="t238" class="section">Result Set - TDS 5.0 (0xEE 238)</h2>
1467
1468 <pre>
1469 INT16 INT16 variable size
1470 +---------+------------+-----------------+
1471 | length | number of | column info |
1472 | | columns | |
1473 +---------+------------+-----------------+
1474
1475
1476 Fields:
1477 length length of message following this field
1478 number of columns number of columns in the result set, this many column
1479 information fields will follow.
1480 column info <a href="#colInfo">column info</a>
1481 </pre>
1482
1483 <hr>
1484 <h2>Done Packets</h2>
1485 <P>
1486 <a name="t253"></a>
1487 <a name="t254"></a>
1488 <a name="t255"></a>
1489 <b class="section">Result Set Done (0xFD 253)</b><br>
1490 <b class="section">Process Done (0xFE 254)</b><br>
1491 <b class="section">Done Inside Process (0xFF 255)</b><br>
1492
1493 <pre>
1494 INT16 INT16 INT32 INT64
1495 +-----------+---------+----------------------+---------------------+
1496 | bit flags | unknown | row count (TDS 7.1-) | row count (TDS 7.2) |
1497 +-----------+---------+----------------------+---------------------+
1498
1499 Fields:
1500 bit flags 0x01 more results
1501 0x02 error (like invalid sql syntax)
1502 0x10 row count is valid
1503 0x20 cancelled
1504 unknown 2,0 /* something to do with block size perhaps */
1505 row count number of rows affected / returned in the result set.
1506 row count is 64-bit using TDS 7.2.
1507 (FIXME check if "affected / returned" is correct)
1508 </pre>
1509 <P>
1510 "Result Set Complete" is the end of a query that doesn't create a process
1511 on the server. I.e., it doesn't call a stored procedure.
1512 <br>
1513 "Process Done" is the end of a stored procedure
1514 <br>
1515 "Done In Process" means that a query internal to a stored procedure
1516 has finished, but the stored procedure isn't done overall.
1517 <br>
1518
1519 <hr>
1520
1521 <h2 class="section">Acknowledgements</h2>
1522 <p>
1523 The following people have contributed to this document:
1524 <ul style="list-style-type: none">
1525 <li>Brian Bruns (first draft, protocol discovery)
1526 <li>Brian Wheeler (protocol discovery)
1527 <li>Mark Schaal (second draft)
1528 <li>Frediano Ziglio
1529 </ul>
1530 <p>
1531 (short list)
1532
1533 <h2>Document Status </h2>
1534 <P>$Id: tds.html,v 1.41 2008-11-25 23:38:33 jklowden Exp $</p>
1535 </body>
1536 </html>