| 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 < 10 and Microsoft SQL Server 6.5</td></tr> |
| 18 |
<tr><td align=center>5.0</td><td>Sybase SQL Server >= 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 |
--> Login |
| 68 |
<-- Login acknowledgement |
| 69 |
|
| 70 |
--> INSERT SQL statement |
| 71 |
<-- Result Set Done |
| 72 |
|
| 73 |
--> SELECT SQL statement |
| 74 |
<-- Column Names |
| 75 |
<-- Column Info |
| 76 |
<-- Row Result |
| 77 |
<-- Row Result |
| 78 |
<-- Result Set Done |
| 79 |
|
| 80 |
--> call stored procedure |
| 81 |
<-- Column Names |
| 82 |
<-- Column Info |
| 83 |
<-- Row Result |
| 84 |
<-- Row Result |
| 85 |
<-- Done Inside Process |
| 86 |
<-- Column Names |
| 87 |
<-- Column Info |
| 88 |
<-- Row Result |
| 89 |
<-- Row Result |
| 90 |
<-- Done Inside Process |
| 91 |
<-- Return Status |
| 92 |
<-- 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 & 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 <database> 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 <= 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 — which is also the last byte of the row buffer — 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 — if there are data between the (presumed) end of the last column and the start of the offset table — 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> </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> </td> |
| 842 |
</tr> |
| 843 |
<tr> |
| 844 |
<td>0xA8</td><td>168</td><td><a href="#t168">Compute Result</a></td><td> </td> |
| 845 |
</tr> |
| 846 |
<tr> |
| 847 |
<td>0xA9</td><td>169</td><td><a href="#t169">Order By</a></td><td> </td> |
| 848 |
</tr> |
| 849 |
<tr> |
| 850 |
<td>0xAA</td><td>170</td><td><a href="#t170">Error Message</a></td><td> </td> |
| 851 |
</tr> |
| 852 |
<tr> |
| 853 |
<td>0xAB</td><td>171</td><td><a href="#t171">Info Message</a></td><td> </td> |
| 854 |
</tr> |
| 855 |
<tr> |
| 856 |
<td>0xAC</td><td>172</td><td><a href="#t172">Output Parameters</a></td><td> </td> |
| 857 |
</tr> |
| 858 |
<tr> |
| 859 |
<td>0xAD</td><td>173</td><td><a href="#t173">Login Acknowledgement</a></td><td> </td> |
| 860 |
</tr> |
| 861 |
<tr> |
| 862 |
<td>0xAE</td><td>174</td><td><a href="#t174">Control</a></td><td> </td> |
| 863 |
</tr> |
| 864 |
<tr> |
| 865 |
<td>0xD1</td><td>209</td><td><a href="#t209">Data --- Row Result</a></td><td> </td> |
| 866 |
</tr> |
| 867 |
<tr> |
| 868 |
<td>0xD3</td><td>211</td><td><a href="#t211">Data --- Compute Result</a></td><td> </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> </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> </td> |
| 899 |
</tr> |
| 900 |
<tr> |
| 901 |
<td>0xFE</td><td>254</td><td><a href="#t254">Process Done</a></td><td> </td> |
| 902 |
</tr> |
| 903 |
<tr> |
| 904 |
<td>0xFF</td><td>255</td><td><a href="#t255">Done inside Process</a></td><td> </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"> </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"> </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"> </a> <hr> |
| 937 |
<h2 class="section">"Logout" (0x71 113)</h2> |
| 938 |
<p> |
| 939 |
No information. (1 byte, value=0 ?) |
| 940 |
|
| 941 |
<a name="t121"> </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"> </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"> </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"> </a> <hr> |
| 975 |
<h2 class="subsection">Cursor Delete - TDS 5.0 (0x81 129)</h2> |
| 976 |
|
| 977 |
<p>TODO. |
| 978 |
|
| 979 |
<a name="t129"> </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 >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 -> 01 04 00 ucs2le "name" |
| 1193 |
db..name -> 03 02 00 ucs2le "db" 00 00 04 00 ucs2le "name" |
| 1194 |
db.dbo.name -> 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"> </a> |
| 1229 |
<a name="t174"> </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"> </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 > 10, a message if level <= 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"> </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"> </a> |
| 1357 |
<a name="t211"> </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> |