 |
 |
Home | FAQ | Documentation | Links | License | Project Info | Download | News | Bugs | Forums
TDS 7.0 protocol documentation
This is a try to document the TDS protocol version 7.0. It has a lot of holes, but it contains a lot of new information over the documentation furnished by the FreeTDS project.
All this is obtained through reverse engineering and it would be pretty interesting to match it against the TDS 5.0 documentation, for example. I'm convinced a lot of new information could be discovered. If anyone is interested in doing this, please contact the project. We'll be more than happy to provide you with any documentation we have gathered so far and that cannot be posted here from various reasons.
0x79 (121) TDS_RETURNSTATUS
Return status of a stored procedure. Precedes TDS_DONEPROC packets (or TDS_PARAM packets, if the stored procedure has output parameters).
| 00 | 1 | 79 | Packet type (TDS_RETURNSTATUS) |
| 01 | 4 | XX XX XX XX | Return status |
0x81 (129) TDS_COLMETADATA
Column meta data information for results (column names, types etc.).
| 00 | 1 | 81 | Packet type (TDS_COLMETADATA) |
| 01 | 2 | XX XX | Number of columns |
| XX | 2 | XX XX | Column usertype id (0 for base types) |
| XX | 2 | XX XX | Column flags (0x01=is nullable, 0x02=case sensitive, 0x04|0x08=is writable, 0x10=auto increment, 0x20=computed) |
| XX | 1 | XX | Column data type |
| XX | X | XX XX XX... | Column size (variable size data only): 4 bytes for TEXT, NTEXT and IMAGE data, 2 bytes for large data (type & 0x80 != 0), 1 byte for other data |
| XX | 2 | XX XX | Column table length (TEXT, NTEXT and IMAGE data only) |
| XX | X | XX XX XX... | Column table name -- fully qualified if database or user different than current (TEXT, NTEXT and IMAGE data only) |
| XX | 1 | XX | Column precision (NUMERIC and DECIMAL types only) |
| XX | 1 | XX | Column scale (NUMERIC and DECIMAL types only) |
| XX | 1 | XX | Column name length |
| XX | X | XX XX XX... | Column name |
Marked fields are repeated for each column.
0x88 (136) TDS_ALTCOLMETADATA (?)
Column meta data information for compute clause.
| 00 | 1 | 88 | Packet type (TDS_ALTCOLMETADATA (?)) |
| 01 | 2 | XX XX | Number of columns |
| 03 | 2 | 01 00 | Unknown |
| 05 | 1 | XX | Number of columns specified in BY clause (e.g. for COMPUTE ... BY a, b this is 2) |
| XX | 2 | XX XX | Index of column specified in BY clause (number of columns specified by above value) |
| XX | 1 | XX | Type of aggregation (0x30=STDEV, 0x31=STDEVP, 0x32=VAR, 0x33=VARP, 0x4B=COUNT, 0x4D=SUM, 0x4F=AVG, 0x51=MIN, 0x52=MAX) |
| XX | 2 | XX XX | Column index (in TDS_COLMETADATA packet) |
| XX | 2 | XX XX | Column usertype id (0 for base types) |
| XX | 2 | XX XX | Column flags (?) |
| XX | 1 | XX | Column data type |
| XX | 1 | XX | Column size (variable size data types only) |
| XX | 1 | XX | Column precision (NUMERIC and DECIMAL types only) |
| XX | 1 | XX | Column scale (NUMERIC and DECIMAL types only) |
| XX | 1 | XX | Column name length (this is probably always 0, so the next field's existence is questionable) |
| XX | X | XX XX XX... | Column name |
Marked fields are repeated for each column.
0xA4 (164) TDS_TABNAME
Table name information for result columns. Follows immediately after a TDS_COLMETADATA packet. Returned by SELECT .. FOR BROWSE queries.
| 00 | 1 | A4 | Packet type (TDS_TABNAME) |
| 01 | 2 | XX XX | Packet size |
| XX | 2 | XX XX | Table name length |
| XX | X | XX XX XX ... | Table name (can be fully qualified with no quoting, e.g. 'myDb..my Table') |
Marked fields are repeated for each table from whose columns appear in the result (watch packet size for end).
0xA5 (165) TDS_COLINFO
Supplementary meta data information for result columns. Follows immediately after a TDS_TABNAME packet. Returned by SELECT .. FOR BROWSE queries.
| 00 | 1 | A5 | Packet type (TDS_COLINFO) |
| 01 | 2 | XX XX | Packet size |
| XX | 1 | XX | Column index (in TDS_COLMETADATA packet, 1-based) |
| XX | 1 | XX | Column table index (in TDS_TABNAME packet, 1-based) |
| XX | 1 | XX | Column flags (0x04=expression, 0x08=key, 0x10=hidden, 0x20=name present) |
| XX | 1 | XX | Column name length (only present if flags & 0x20 != 0) |
| XX | X | XX XX XX ... | Column name (only present if flags & 0x20 != 0, the TDS_COLMETADATA contains the label/alias) |
Marked fields are repeated for each column coming from some table (watch packet size for end).
0xA9 (169) TDS_ORDER
Column ordering information. Follows after a TDS_COLMETADATA packet (or its associated TDS_COLINFO packet).
The list of columns indexes after which the result is ordered (1-based).
| 00 | 1 | A9 | Packet type (TDS_ORDER) |
| 01 | 2 | XX XX | Packet size |
| XX | 2 | XX XX | Column index |
Marked fields are repeated for each column the result is ordered by.
0xAA (170) TDS_ERROR
0xAB (171) TDS_INFO
Error (TDS_ERROR) or message/warning (TDS_INFO).
| 00 | 1 | AA/AB | Packet type (TDS_ERROR/TDS_INFO) |
| 01 | 2 | XX XX | Packet size |
| 03 | 4 | XX XX XX XX | Error message number |
| 07 | 1 | XX | State (error dependent) |
| 08 | 1 | XX | Severity (0,1=information, 2-18=warnings-regular errors, 19-25=fatal errors, connection is usually closed |
| 09 | 2 | XX XX | Message length |
| 0B | X | XX XX XX ... | Message |
| XX | 1 | XX | Server name length |
| XX | X | XX XX XX ... | Server name |
| XX | 1 | XX | Procedure name length |
| XX | X | XX XX XX ... | Procedure name |
| XX | 1 | XX | Line number |
| XX | 1 | XX | Unknown (usually 0x00) |
0xAC (172) TDS_PARAM
Stored procedure output parameter (one such package is returned for each output parameter). Precedes TDS_ENDPROC. WRITETEXT seems to also generate a TDS_PARAM with a formal type of 0x00.
Parameter type seems to be exactly the type submitted (e.g. if the formal parameter is of type NVARCHAR and the actual parameter sent is of type VARCHAR the output parameter will have type VARCHAR).
| 00 | 1 | AC | Packet type (TDS_PARAM) |
| 01 | 2 | XX XX | Packet size |
| 03 | 1 | XX | Parameter name length |
| 04 | X | XX XX XX ... | Parameter name |
| XX | 1 | 01 | Unknown (seems to always be 0x01) |
| XX | 1 | XX | Formal type (the type that is declared in the stored procedure header); for TDS_PARAM packets returned by WRITETEXT seems to be 0x00 |
| XX | 3 | 00 00 00 | Unknown (seem to always be set to 0x00) |
| XX | 1 | XX | Actual column type |
| XX | X | XX ... | Maximum size (only variable size types): 2 bytes for large types (type & 0x80 != 0), 1 byte for all other types (TEXT, NTEXT and IMAGE cannot be parameters) |
| XX | 1 | XX | Precision (only DECIMAL and NUMERIC data) |
| XX | 1 | XX | Scale (only DECIMAL and NUMERIC data) |
| XX | X | XX ... | Actual size (only variable size types): 2 bytes for large types (type & 0x80 != 0), 1 byte for all other types (TEXT, NTEXT and IMAGE cannot be parameters) |
| XX | X | XX XX XX ... | Parameter value |
0xAD (173) TDS_LOGINACK
Login acknowledge.
| 00 | 1 | AD | Packet type (TDS_LOGINACK) |
| 01 | 2 | XX XX | Packet size |
| 03 | 1 | XX | Unknown (usually 0x01) |
| 04 | 4 | XX XX 00 00 | TDS version (e.g. 07 00 00 00 for 7.0) |
| 08 | 1 | XX | Database product name length |
| 09 | X | XX XX XX ... | Database product name |
| XX | 4 | XX XX XX XX | Database product version (e.g. 07 00 02 6F stands for 07.00.0879 -- 879 = (0x02+1)<<8 | 0x6F -- quite weird) |
0xD1 (209) TDS_ROW
One result row.
| 00 | 1 | D1 | Packet type (TDS_ROW) |
| XX | X | XX XX XX ... | Column data (size and format depend on the column's data type) |
Marked fields are repeated for each column.
0xD3 (211) TDS_ALTROW
One aggregate row.
| 00 | 1 | D3 | Packet type (TDS_ALTROW) |
| 01 | 2 | 01 00 | Unknown (probably some count) |
| XX | X | XX XX XX ... | Column data (size and format depend on the column's data type) |
Marked fields are repeated for each column.
0xE3 (226) TDS_ENVCHANGE
Environment change information.
| 00 | 1 | E3 | Packet type (TDS_ENVCHANGE) |
| 01 | 2 | XX XX | Packet size |
| 03 | 1 | XX | Envchange type (01=Database, 02=Language 03=Charset, 04=Blocksize, 05=Unicode locale id, 06=Unicode comparison style) |
| 04 | X | XX XX XX ... | Envchange info |
Envchange type 01=Database change
| 03 | 1 | 01 | Database change |
| 04 | 1 | XX | New database name length |
| 05 | X | XX XX XX ... | New database name |
| XX | 1 | XX | Old database name length |
| XX | X | XX XX XX ... | Old database name |
Envchange type 03=Charset change
| 03 | 1 | 03 | Charset change |
| 04 | 1 | XX | Charset length |
| 05 | X | XX XX XX ... | Charset |
| XX | 2 | XX XX | Unknown (maybe charset code) |
| XX | X | XX XX XX ... | Probably old charset (length, then value) |
Envchange type 04=Blocksize change
| 03 | 1 | 03 | Blocksize change |
| 04 | 1 | XX | Blocksize length |
| 05 | X | XX XX XX ... | Blocksize (as string) |
| XX | X | XX XX XX ... | Probably old blocksize (length, then value) |
0xFD (253) TDS_DONE
0xFE (254) TDS_DONEPROC
0xFF (255) TDS_DONEINPROC
End of query execution outside stored procedure (TDS_DONE), end of stored procedure (TDS_DONEPROC) or end of query inside a stored procedure (TDS_DONEINPROC).
| 00 | 1 | FD/FE/FF | Packet type (TDS_DONE/TDS_DONEPROC/TDS_DONEINPROC) |
| 01 | 2 | XX XX | Status flags (0x01=more results, 0x02=error, 0x10=valid update count, 0x20=cancel ack) |
| 03 | 2 | XX XX | Operation (see below) |
| 05 | 4 | XX XX XX XX | Row count (number of rows affected by query) |
Values for Operation field (incomplete, uncertain)
| B9 | IMPLICIT TRANSACTIONS ON |
| BA | IMPLICIT TRANSACTIONS OFF |
| C0 | IF (block start) (?) |
| C1 | SELECT |
| C3 | INSERT |
| C4 | DELETE |
| C5 | UPDATE |
| C6 | CREATE TABLE |
| C7 | DROP TABLE failed |
| CA | ENDIF (block end) (?) |
| D2 | ROLLBACK |
| D5 | COMMIT |
| D8 | ALTER TABLE |
| DE | CREATE PROCEDURE |
| DF | DROP PROCEDURE |
| E0 | END PROCEDURE (the only value for TDS_DONEPROC, but can also be a value for TDS_DONEINPROC if the procedure was called inside another procedure) |
| E2 | USE (database change) |
| F9 | SET (transaction isolation level, language, ...) |
| FD | Syntax error or batch execution interrupted (really not sure) |
|
Project hosted by |
Web Design Anca Sinpalean |