Tabular Data Stream: The Binding Between Client and SAP ASE
SAPTechnology
2,592 views
97 slides
Jun 11, 2015
Slide 1 of 97
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
About This Presentation
This lecture will cover the TDS (Tabular Data Stream) protocol in different aspects. We'll cover in brief form its history and then go over the specfication via a walk through method so the learner can understand how to navigate the documentation. Critical aspects of the protocol such as login, ...
Slide Content
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS Protocol Reference
TDS - Tabular Data Stream
Reference and information to assist in
diagnosing client/server problems
Author: Paul Vero
March, 2015
Group
Annual Conference, 2015
TDS Protocol Reference
TDS - Tabular Data Stream
Reference and information to assist in
diagnosing client/server problems
Author: Paul Vero
March, 2015
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Built-in tracing
Tokens
Login
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Built-in tracing
Tokens
Login
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens
Login
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens
Login
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Introduction to TDS
•You always hear the Engineer or PSE say “Get me a
TDS trace!”
•“What the heck is that?”
•Tabular Data Stream
•It’s a token based, application level protocol used to
send requests and responses between clients and
servers
•Relies on a connection oriented transport service
•TCPIP provides such a transport
Group
Annual Conference, 2015
Introduction to TDS
•You always hear the Engineer or PSE say “Get me a
TDS trace!”
•“What the heck is that?”
•Tabular Data Stream
•It’s a token based, application level protocol used to
send requests and responses between clients and
servers
•Relies on a connection oriented transport service
•TCPIP provides such a transport
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Versions
•The TDS Specification has been around a long time
•Started with 4.X (4.2)
•The earliest I’m aware of is TDS 4.0, 1988
•Current TDS version is 5.0
•Released mid to late 90s
•Revisions are labeled as 3.X
•Latest revision is 3.9
•February 2007
Group
Annual Conference, 2015
Versions
•The TDS Specification has been around a long time
•Started with 4.X (4.2)
•The earliest I’m aware of is TDS 4.0, 1988
•Current TDS version is 5.0
•Released mid to late 90s
•Revisions are labeled as 3.X
•Latest revision is 3.9
•February 2007
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens
Login
Message Header
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens
Login
Message Header
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS documentation
•This is the internal TDS webpage:
•http://www-ocsd/TDS/
•Contains the current version:
•http://www-ocsd/TDS/specification/39/tds39.pdf
•Previous and archived versions
•The specification is in PDF format
•Contains reference for every supported TDS token
Group
Annual Conference, 2015
TDS documentation
•This is the internal TDS webpage:
•http://www-ocsd/TDS/
•Contains the current version:
•http://www-ocsd/TDS/specification/39/tds39.pdf
•Previous and archived versions
•The specification is in PDF format
•Contains reference for every supported TDS token
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS documentation
•This section will provide additional information as it
walks through the TDS specification document,
tds39.pdf
•This is a high level explanation of the sections
•This is useful to understand the concept of a
client/server protocol
•This presentation will not cover every chapter in the
specification
•Guide to get you used to the document
Group
Annual Conference, 2015
TDS documentation
•This section will provide additional information as it
walks through the TDS specification document,
tds39.pdf
•This is a high level explanation of the sections
•This is useful to understand the concept of a
client/server protocol
•This presentation will not cover every chapter in the
specification
•Guide to get you used to the document
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
What uses TDS?
•Any client for ASE uses TDS
•Think of it as the communicating protocol between
server (ASE) and client
•Supported clients:
•Open Client/Server
•ASE Data Access Products:
•ODBC, OLE DB, ADO.NET, jConnect
•Proxy servers and gateways:
•ASE/CIS, DirectConnect, Replication Server, IQ
Group
Annual Conference, 2015
What uses TDS?
•Any client for ASE uses TDS
•Think of it as the communicating protocol between
server (ASE) and client
•Supported clients:
•Open Client/Server
•ASE Data Access Products:
•ODBC, OLE DB, ADO.NET, jConnect
•Proxy servers and gateways:
•ASE/CIS, DirectConnect, Replication Server, IQ
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS Model
SQLConnect();
SQLExecute()
SQLFetch();
ODBC Code
O
D
B
C
A
P
I
T
D
S
/
N
e
t
w
o
r
k
L
a
y
e
r
ODBC Driver
TDS/Network Layer
TDS LOGIN record
User name: sa
Password: []
Charset: cp850
Group
Annual Conference, 2015
TDS Model
SQLConnect();
SQLExecute()
SQLFetch();
ODBC Code
O
D
B
C
A
P
I
T
D
S
/
N
e
t
w
o
r
k
L
a
y
e
r
ODBC Driver
TDS/Network Layer
TDS LOGIN record
User name: sa
Password: []
Charset: cp850
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Sample TDS Session
•When client is going to connect to
ASE it first requests a transport
connection (TCPIP) to the ASE server.
•Client then sends the login record to
start the dialog.
•ASE responds with an
acknowledgement token
(TDS_LOGINACK) along with a
completion token (TDS_DONE).
•When this occurs, it means the ASE
accepts the dialog with this client
Group
Annual Conference, 2015
Sample TDS Session
•When client is going to connect to
ASE it first requests a transport
connection (TCPIP) to the ASE server.
•Client then sends the login record to
start the dialog.
•ASE responds with an
acknowledgement token
(TDS_LOGINACK) along with a
completion token (TDS_DONE).
•When this occurs, it means the ASE
accepts the dialog with this client
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Sample TDS Session
•With the established dialog the client
is now ready to send SQL statements
to the ASE.
•To send a SELECT statement client sends
it in TDS_LANGUAGE token.
•ASE executes the query and sends the
following:
•Column description of result set
•Data rows follow this
•TDS_DONE completion packet, with row count
is the last thing sent
Group
Annual Conference, 2015
Sample TDS Session
•With the established dialog the client
is now ready to send SQL statements
to the ASE.
•To send a SELECT statement client sends
it in TDS_LANGUAGE token.
•ASE executes the query and sends the
following:
•Column description of result set
•Data rows follow this
•TDS_DONE completion packet, with row count
is the last thing sent
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Sample TDS Session
Login request
Client Server (ASE)
hostname: asehost
Username: tdsuser
Password: tdspwd
tds version: 5.0
Application: ado.net
Login request
TDS_LOGINACK
TDS_DONE
Group
Annual Conference, 2015
Sample TDS Session
Login request
Client Server (ASE)
hostname: asehost
Username: tdsuser
Password: tdspwd
tds version: 5.0
Application: ado.net
Login request
TDS_LOGINACK
TDS_DONE
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Sending TDS request
Language Request
Client Server (ASE)
- Execute: Select * from
authors
- Process the rows when
Received
- Stop processing when
Last row is received
TDS_LANGUAGE
TDS_ROWFMT
TDS_DONE
TDS_ROW, TDS_ROW, …
Group
Annual Conference, 2015
Sending TDS request
Language Request
Client Server (ASE)
- Execute: Select * from
authors
- Process the rows when
Received
- Stop processing when
Last row is received
TDS_LANGUAGE
TDS_ROWFMT
TDS_DONE
TDS_ROW, TDS_ROW, …
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Protocol Data Units - PDUs
PDU is used to contain the request (client) and response
(server)
The request/response might span several PDUs
Size of the PDU is decided between client and server
during the login time (dialog establishment)
PDU contains header and usually data
Group
Annual Conference, 2015
Protocol Data Units - PDUs
PDU is used to contain the request (client) and response
(server)
The request/response might span several PDUs
Size of the PDU is decided between client and server
during the login time (dialog establishment)
PDU contains header and usually data
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
•PDU Header
•PDU header contains information about the size and
contents of the PDU as well as an indication if this is the
last PDU in a request/response
•Described in the Message Buffer Header reference page
•TDS is half-duplex
•Client writes complete request
•Then it reads complete response from server
•None are intermixed and multiple requests can’t
be outstanding (active results pending)
Group
Annual Conference, 2015
•PDU Header
•PDU header contains information about the size and
contents of the PDU as well as an indication if this is the
last PDU in a request/response
•Described in the Message Buffer Header reference page
•TDS is half-duplex
•Client writes complete request
•Then it reads complete response from server
•None are intermixed and multiple requests can’t
be outstanding (active results pending)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
PDU Header
Group
Annual Conference, 2015
PDU Header
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
PDU Header
•Before going further there are some
tools to be discussed in detail later,
used to format PDUs in readable
format
•RAW format displays data similar to that
found in sniffer trace
•Use ASE traceflags on command-line:
•4001, 4013, 4034, 4035 and 4036
•4013 prints out the login record contents
•Information goes to stdout so might want to use
tee
•Formated output with RIBO
•Java based TDS packet gateway tool
Group
Annual Conference, 2015
PDU Header
•Before going further there are some
tools to be discussed in detail later,
used to format PDUs in readable
format
•RAW format displays data similar to that
found in sniffer trace
•Use ASE traceflags on command-line:
•4001, 4013, 4034, 4035 and 4036
•4013 prints out the login record contents
•Information goes to stdout so might want to use
tee
•Formated output with RIBO
•Java based TDS packet gateway tool
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Raw TDS from ASE
R STRMBUF: socket 5 status 0x20000 size 2048 left 504
TDS type TDSLOGIN (2) status 0 len 512 chan 0 pack 0 win 0
2020AAA0 ( 0): 02000002 00000000 50564552 4f585000 00000000 ........PVEROXP.....
2020AAB4 ( 20): 00000000 00000000 00000000 00000000 00000773 ...................s
2020AAC8 ( 40): 61000000 00000000 00000000 00000000 00000000 a...................
2020AADC ( 60): 00000000 00000000 00020000 00000000 00000000 ....................
2020AAF0 ( 80): 00000000 00000000 00000000 00000000 00000000 ....................
2020AB04 ( 100): 00353836 34000000 00000000 00000000 00000000 .5864...............
2020AB18 ( 120): 00000000 00000000 00000004 0301060a 09010100 ....................
2020AB2C ( 140): 00000000 00000000 6973716c 00000000 00000000 ........isql........
2020AB40 ( 160): 00000000 00000000 00000000 00000000 00000472 ...................r
2020AB54 ( 180): 69626f00 00000000 00000000 00000000 00000000 ibo.................
2020AB68 ( 200): 00000000 00000000 00040000 00000000 00000000 ....................
2020AB7C ( 220): 00000000 00000000 00000000 00000000 00000000 ....................
2020AB90 ( 240): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABA4 ( 260): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABB8 ( 280): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABCC ( 300): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABE0 ( 320): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABF4 ( 340): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC08 ( 360): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC1C ( 380): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC30 ( 400): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC44 ( 420): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC58 ( 440): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC6C ( 460): 00000000 00020500 00004354 2d4c6962 72617279 ..........CT-Library
2020AC80 ( 480): 0a0f0000 00000d11 00735f65 6e676c69 73680000 .........s_english..
2020AC94 ( 500): 00000000 00000000 00000000 ............
Hostname
TDS Version 5.0.0.0
Application
Group
Annual Conference, 2015
Raw TDS from ASE
R STRMBUF: socket 5 status 0x20000 size 2048 left 504
TDS type TDSLOGIN (2) status 0 len 512 chan 0 pack 0 win 0
2020AAA0 ( 0): 02000002 00000000 50564552 4f585000 00000000 ........PVEROXP.....
2020AAB4 ( 20): 00000000 00000000 00000000 00000000 00000773 ...................s
2020AAC8 ( 40): 61000000 00000000 00000000 00000000 00000000 a...................
2020AADC ( 60): 00000000 00000000 00020000 00000000 00000000 ....................
2020AAF0 ( 80): 00000000 00000000 00000000 00000000 00000000 ....................
2020AB04 ( 100): 00353836 34000000 00000000 00000000 00000000 .5864...............
2020AB18 ( 120): 00000000 00000000 00000004 0301060a 09010100 ....................
2020AB2C ( 140): 00000000 00000000 6973716c 00000000 00000000 ........isql........
2020AB40 ( 160): 00000000 00000000 00000000 00000000 00000472 ...................r
2020AB54 ( 180): 69626f00 00000000 00000000 00000000 00000000 ibo.................
2020AB68 ( 200): 00000000 00000000 00040000 00000000 00000000 ....................
2020AB7C ( 220): 00000000 00000000 00000000 00000000 00000000 ....................
2020AB90 ( 240): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABA4 ( 260): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABB8 ( 280): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABCC ( 300): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABE0 ( 320): 00000000 00000000 00000000 00000000 00000000 ....................
2020ABF4 ( 340): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC08 ( 360): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC1C ( 380): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC30 ( 400): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC44 ( 420): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC58 ( 440): 00000000 00000000 00000000 00000000 00000000 ....................
2020AC6C ( 460): 00000000 00020500 00004354 2d4c6962 72617279 ..........CT-Library
2020AC80 ( 480): 0a0f0000 00000d11 00735f65 6e676c69 73680000 .........s_english..
2020AC94 ( 500): 00000000 00000000 00000000 ............
Hostname
TDS Version 5.0.0.0
Application
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Login record from ASE errorlog
This traceflag, 4013 is useful to get the contents of the login
record:
00:00000:00017:2008/04/10 22:27:26.30 server Received LOGINREC
LOGINREC at 0x20AF7D88
host=`PVEROXP' user=`sa' hostproc=`5864'
int2=3 int4=1 char=6 flt=10 date=9
usedb=1 dmpld=1 interface=0 netconn_type=0
appname=`isql' servername=`ribo'
tds_vers=(5.0.0.0) progname=`CT-Library' prog_vers=(15.0.0.0)
noshort=0 flt4=13 date4=17
language=`' setlang=0
SECURITY: hier=0 e2e option: 0x00 db bulk reserved: 0x00
HA: ssn option: 0x08 ssn handle:(0x00, 0x00, 0x00, 0x00, 0x00, 0x00)
UNUSED: slunused:(0x00)
role=0
charset=`iso_1' setcharset=0 packetsize=`512'
00:00000:00017:2008/04/10 22:27:26.30 server login: sa PVEROXP, spid: 17, kpid:
1245203 (0x130013)
Group
Annual Conference, 2015
Login record from ASE errorlog
This traceflag, 4013 is useful to get the contents of the login
record:
00:00000:00017:2008/04/10 22:27:26.30 server Received LOGINREC
LOGINREC at 0x20AF7D88
host=`PVEROXP' user=`sa' hostproc=`5864'
int2=3 int4=1 char=6 flt=10 date=9
usedb=1 dmpld=1 interface=0 netconn_type=0
appname=`isql' servername=`ribo'
tds_vers=(5.0.0.0) progname=`CT-Library' prog_vers=(15.0.0.0)
noshort=0 flt4=13 date4=17
language=`' setlang=0
SECURITY: hier=0 e2e option: 0x00 db bulk reserved: 0x00
HA: ssn option: 0x08 ssn handle:(0x00, 0x00, 0x00, 0x00, 0x00, 0x00)
UNUSED: slunused:(0x00)
role=0
charset=`iso_1' setcharset=0 packetsize=`512'
00:00000:00017:2008/04/10 22:27:26.30 server login: sa PVEROXP, spid: 17, kpid:
1245203 (0x130013)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS output from RIBO
Capture Record Header
Source [4]: REQUEST (0x00000001)
Length [4]: 615
PDU Header
TDS Packet Type [1]: BUF_LOGIN (0x02)
Status [1]: BUFSTAT_BEGIN (0x00)
Length [2]: 512
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
PDU Header
TDS Packet Type [1]: BUF_LOGIN (0x02)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 103
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
Group
Annual Conference, 2015
TDS output from RIBO
Capture Record Header
Source [4]: REQUEST (0x00000001)
Length [4]: 615
PDU Header
TDS Packet Type [1]: BUF_LOGIN (0x02)
Status [1]: BUFSTAT_BEGIN (0x00)
Length [2]: 512
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
PDU Header
TDS Packet Type [1]: BUF_LOGIN (0x02)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 103
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
PDU Data
•PDUs usually include some data
•Control PDUs don’t contain data
•Only header
•Request and Response PDUs contain TDS tokens
•Tokens describe the request/response
•Data is exchanged so the client and server can
complete the request/response cycle
Group
Annual Conference, 2015
PDU Data
•PDUs usually include some data
•Control PDUs don’t contain data
•Only header
•Request and Response PDUs contain TDS tokens
•Tokens describe the request/response
•Data is exchanged so the client and server can
complete the request/response cycle
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDUs - Requests
Dialog establishment information
Language command
Cursor command
Database Remote Procedure Call
Attentions
Dynamic SQL command
Message command
Group
Annual Conference, 2015
Client PDUs - Requests
Dialog establishment information
Language command
Cursor command
Database Remote Procedure Call
Attentions
Dynamic SQL command
Message command
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Dialog Establishment
•Create a transport connection
•This is a low level network function
•TCPIP and socket establishment
•Send Login record
•Credentials and properties
•Send Capability data stream
•What can I (client) do, what can’t I do
•What can and can’y you (the Server) do
•Authentication handshaking
•Password encryption
•Read login acknowledgement
Group
Annual Conference, 2015
Client PDU: Dialog Establishment
•Create a transport connection
•This is a low level network function
•TCPIP and socket establishment
•Send Login record
•Credentials and properties
•Send Capability data stream
•What can I (client) do, what can’t I do
•What can and can’y you (the Server) do
•Authentication handshaking
•Password encryption
•Read login acknowledgement
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Language Commands
•Send a SQL Command to the ASE
•TDS_LANGUAGE token is used to send the command
•Command may span multiple PDUs
•Limited by length field in the TDS_LANGUAGE token
Group
Annual Conference, 2015
Client PDU: Language Commands
•Send a SQL Command to the ASE
•TDS_LANGUAGE token is used to send the command
•Command may span multiple PDUs
•Limited by length field in the TDS_LANGUAGE token
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Cursor Commands
•There are two ways to send cursor commands
•Language commands
•TDS_LANGUAGE
•Send to ASE using Cursor T-SQL commands
•Other servers require to parse language and be able
to implement the cursor language
•Cursor TDS tokens
•ANSI specified cursor operations
•Native token support
•Efficient by eliminating parsing sequence
•TDS_CUR* tokens
Group
Annual Conference, 2015
Client PDU: Cursor Commands
•There are two ways to send cursor commands
•Language commands
•TDS_LANGUAGE
•Send to ASE using Cursor T-SQL commands
•Other servers require to parse language and be able
to implement the cursor language
•Cursor TDS tokens
•ANSI specified cursor operations
•Native token support
•Efficient by eliminating parsing sequence
•TDS_CUR* tokens
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Remote Procedure Calls
(RPC)
•Client sends TDS_DBRPC token
•Binary stream containing:
•Rpc NAME
•Options
•Parameters
•No intermixing with SQL commands or other RPC
commands
•Uses additional tokens to define the parameters and to
send parameter values
Group
Annual Conference, 2015
Client PDU: Remote Procedure Calls
(RPC)
•Client sends TDS_DBRPC token
•Binary stream containing:
•Rpc NAME
•Options
•Parameters
•No intermixing with SQL commands or other RPC
commands
•Uses additional tokens to define the parameters and to
send parameter values
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Attention
The client sends an attention signal to the server to
cancel its current request
Client sends attention to server
Client continues to read
Client discards the data read after sending attention
Server acknowledges the attention and now
everyone is happy
Group
Annual Conference, 2015
Client PDU: Attention
The client sends an attention signal to the server to
cancel its current request
Client sends attention to server
Client continues to read
Client discards the data read after sending attention
Server acknowledges the attention and now
everyone is happy
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Client PDU: Dynamic SQL
The client sends TDS_DYNAMIC or TDS_DYNAMIC2 data
stream to execute dynamic SQL on the server.
Stream indicates various functionality:
Prepare
Execute
Deallocate
Arguments included?
SQL Statement and identification
Group
Annual Conference, 2015
Client PDU: Dynamic SQL
The client sends TDS_DYNAMIC or TDS_DYNAMIC2 data
stream to execute dynamic SQL on the server.
Stream indicates various functionality:
Prepare
Execute
Deallocate
Arguments included?
SQL Statement and identification
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Server PDU: Response
Dialog Establishment acknowledgement
Login request is received
Row results
Return status from procedures
Return parameters
Response completion
Error information
Attention acknowledgement
Cursor status
Message responses
Group
Annual Conference, 2015
Server PDU: Response
Dialog Establishment acknowledgement
Login request is received
Row results
Return status from procedures
Return parameters
Response completion
Error information
Attention acknowledgement
Cursor status
Message responses
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
On to the gory details
•The TDS specification documentation contains details
on various topics
•Read these when you’re ready to learn the topic, like
cursors, security, etc.
•Contains descriptions and diagrams of the data flow
Group
Annual Conference, 2015
On to the gory details
•The TDS specification documentation contains details
on various topics
•Read these when you’re ready to learn the topic, like
cursors, security, etc.
•Contains descriptions and diagrams of the data flow
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Built-in tracing
Tokens
Login
Message Header
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Built-in tracing
Tokens
Login
Message Header
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Capture Tools
•Capture tools gather the TDS data stream into a file
•Useful for troubleshooting purposes
•Everyone asks for TDS trace
•Verify activity
•Determine problems
•Isolate whether client or server side
Group
Annual Conference, 2015
Capture Tools
•Capture tools gather the TDS data stream into a file
•Useful for troubleshooting purposes
•Everyone asks for TDS trace
•Verify activity
•Determine problems
•Isolate whether client or server side
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Capture Tools
Ribo
Most common
Formatted
Included in SDK and ASE installs
GuiProx
Useful when needing timestamps
Raw output
Sniffers
Used when unable to get others to work
Necessary if issues with TCP/IP protocol
ASE TDS
Trace options captures raw TDS between server and client
srv.log
Open Server log
Raw TDS
DirectConnect, DCO
Group
Annual Conference, 2015
Capture Tools
Ribo
Most common
Formatted
Included in SDK and ASE installs
GuiProx
Useful when needing timestamps
Raw output
Sniffers
Used when unable to get others to work
Necessary if issues with TCP/IP protocol
ASE TDS
Trace options captures raw TDS between server and client
srv.log
Open Server log
Raw TDS
DirectConnect, DCO
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo
•Ribo is most common tool
•Java based and requires these env variables:
•set RIBO_HOME=%SYBASE%\jutils-2_0\ribo
•set JAVA_HOME=C:\Java\jdk\jdk1.5.0_09
•Just make sure this points to a jvm
•Start up with arguments
•ribo -l 9999 -s %1 -p %2 -gui -c cat -t -
f devfilter.x
Group
Annual Conference, 2015
Ribo
•Ribo is most common tool
•Java based and requires these env variables:
•set RIBO_HOME=%SYBASE%\jutils-2_0\ribo
•set JAVA_HOME=C:\Java\jdk\jdk1.5.0_09
•Just make sure this points to a jvm
•Start up with arguments
•ribo -l 9999 -s %1 -p %2 -gui -c cat -t -
f devfilter.x
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo Gui
Startup Ribo and this is
what you get
Group
Annual Conference, 2015
Ribo Gui
Startup Ribo and this is
what you get
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo Gui
•Preferences provide
options
•Capture file
prefix
•Translation
filter
•Option to
translate
•Option to
display in a
window
•Careful as this
is resource
intensive
Group
Annual Conference, 2015
Ribo Gui
•Preferences provide
options
•Capture file
prefix
•Translation
filter
•Option to
translate
•Option to
display in a
window
•Careful as this
is resource
intensive
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo Gui: Display in Window
Group
Annual Conference, 2015
Ribo Gui: Display in Window
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo is a TDS gateway
Client Workstation
Host: Rain
Connect to Ribo Host
ASE Server
Host: Snow
Port: 1502
Ribo Host: Rain
Port: 9999
Connects to
Snow,1502
Ribo –l 9999 –s Snow –p 1502 –gui …
Group
Annual Conference, 2015
Ribo is a TDS gateway
Client Workstation
Host: Rain
Connect to Ribo Host
ASE Server
Host: Snow
Port: 1502
Ribo Host: Rain
Port: 9999
Connects to
Snow,1502
Ribo –l 9999 –s Snow –p 1502 –gui …
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
RIBO environment
Windows example:
set RIBO_HOME=c:\sap\syb157\jutils-3_0\ribo
set JAVA_HOME=C:\sap\syb157\jre64
set PATH=%JAVA_HOME%\bin;%PATH%
c:
cd %RIBO_HOME%
Group
Annual Conference, 2015
RIBO environment
Windows example:
set RIBO_HOME=c:\sap\syb157\jutils-3_0\ribo
set JAVA_HOME=C:\sap\syb157\jre64
set PATH=%JAVA_HOME%\bin;%PATH%
c:
cd %RIBO_HOME%
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Sample RIBO Output
Connect to ASE 15.0.2
Issue select @@version
Issue select * from tds_table
Table has int, varchar, decimal and datetime
Call stored procedure
sp_test @a, @b
@b is output parameter
Group
Annual Conference, 2015
Sample RIBO Output
Connect to ASE 15.0.2
Issue select @@version
Issue select * from tds_table
Table has int, varchar, decimal and datetime
Call stored procedure
sp_test @a, @b
@b is output parameter
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Select @@version
Group
Annual Conference, 2015
Select @@version
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Ribo trace walkthrough: Login
Record
Login Record; fixed length.
Host Name [30]: "PVEROXP"
Host Name Length [1]: 7
User Name [30]: "sa"
User Name Length [1]: 2
Password [30]: ""
Password Length [1]: 0
Host Process [30]: "3124"
Host Process Length [1]: 4
Byte Ordering - int2 [1]: 3
Byte Ordering - int4 [1]: 1
Character Encoding [1]: 6
Float Format [1]: 10
Date Format [1]: 9
lusedb [1]: 0x01
ldmpld [1]: 1
linterfacespare [1]: 0x00
Group
Annual Conference, 2015
Ribo trace walkthrough: Login
Record
Login Record; fixed length.
Host Name [30]: "PVEROXP"
Host Name Length [1]: 7
User Name [30]: "sa"
User Name Length [1]: 2
Password [30]: ""
Password Length [1]: 0
Host Process [30]: "3124"
Host Process Length [1]: 4
Byte Ordering - int2 [1]: 3
Byte Ordering - int4 [1]: 1
Character Encoding [1]: 6
Float Format [1]: 10
Date Format [1]: 9
lusedb [1]: 0x01
ldmpld [1]: 1
linterfacespare [1]: 0x00
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Login Record
Dialog Type [1]: 0
lbufsize [1]: 0
spare [3]: 0x000000
Application Name [30]: "isql"
Application Name Length [1]: 4
Service Name [30]: "ribo"
Service Name Length [1]: 4
Remote Passwords [255]:
<universal>/<null>
Remote Passwords Length [1]: 2
TDS Version [4]: 5.0.0.0
Prog Name [30]: "CT-Library"
Prog Name Length [1]: 10
Prog Version [4]: 15.0.0.0
Convert Shorts [1]: 0
4-byte Float Format [1]: 13
4-byte Date Format [1]: 17
Group
Annual Conference, 2015
Login Record
Dialog Type [1]: 0
lbufsize [1]: 0
spare [3]: 0x000000
Application Name [30]: "isql"
Application Name Length [1]: 4
Service Name [30]: "ribo"
Service Name Length [1]: 4
Remote Passwords [255]:
<universal>/<null>
Remote Passwords Length [1]: 2
TDS Version [4]: 5.0.0.0
Prog Name [30]: "CT-Library"
Prog Name Length [1]: 10
Prog Version [4]: 15.0.0.0
Convert Shorts [1]: 0
4-byte Float Format [1]: 13
4-byte Date Format [1]: 17
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Login Record
Language [30]: ""
Language Length [1]: 0
Notify when Changed [1]: 0
Old Secure Info [2]: 0x0000
Secure Login Flags [1]: UNUSED
(0x00)
Bulk Copy [1]: 0
HA Login Flags [1]: 0x08
HA Session ID [6]:
0x000000000000
Spare [2]: 0x0000
Character Set [30]: "iso_1"
Character Set Length [1]: 5
Notify when Changed [1]: 0
Packet Size [6]: 512
Packet Size Length [1]: 3
Group
Annual Conference, 2015
Login Record
Language [30]: ""
Language Length [1]: 0
Notify when Changed [1]: 0
Old Secure Info [2]: 0x0000
Secure Login Flags [1]: UNUSED
(0x00)
Bulk Copy [1]: 0
HA Login Flags [1]: 0x08
HA Session ID [6]:
0x000000000000
Spare [2]: 0x0000
Character Set [30]: "iso_1"
Character Set Length [1]: 5
Notify when Changed [1]: 0
Packet Size [6]: 512
Packet Size Length [1]: 3
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Capabilities
CAPABILITY Token (0xE2); variable length.
Length [2]: 28
Type [1]: CAP_REQUEST
Mask [12]:
0x00 (00000000): (MULTI_REQUESTS), (REQ_MIGRATE), (REQ_UNUSED)
0xE9 (11101001): REQ_DBRPC2, REQ_CURINFO3, DATA_XML, (REQ_BLOB_NCHAR_16), REQ_LARGEIDENT, (DATA_SINT1), (CAP_CLUSTERFAILOVER), DATA_UNITEXT
0xB5 (10110101): REQ_SRVPKTSIZE, (CSR_KEYSETDRIVEN), CSR_SEMISENSITIVE, CSR_INSENSITIVE, (CSR_SENSITIVE), CSR_SCROLL, (DATA_INTERVAL), DATA_TIME
0x87 (10000111): DATA_DATE, (BLOB_NCHAR_SCSU), (BLOB_NCHAR_8), (BLOB_NCHAR_16), (IMAGE_NCHAR), DATA_NLBIN, CUR_IMPLICIT, DATA_UINTN
0xE8 (11101000): DATA_UINT8, DATA_UINT4, DATA_UINT2, (REQ_RESERVED2), WIDETABLE, (DATA_COLUMNSTATUS), (OBJECT_BINARY), (REQ_RESERVED1)
0x3B (00111011): (OBJECT_CHAR), (OBJECT_JAVA1), DOL_BULK, DATA_VOID, DATA_INT8, (DATA_BITN), DATA_FLTN, PROTO_DYNPROC
0x61 (01100001): (PROTO_DYNAMIC), DATA_BOUNDARY, DATA_SENSITIVITY, (REQ_URGEVT), (PROTO_BULK), (PROTO_TEXT), (CON_LOGICAL), CON_INBAND
0x7F (01111111): (CON_OOB), CSR_MULTI, CSR_REL, CSR_ABS, CSR_LAST, CSR_FIRST, CSR_PREV, DATA_MONEYN
0xFF (11111111): DATA_DATETIMEN, DATA_INTN, DATA_LBIN, DATA_LCHAR, DATA_DEC, DATA_IMAGE, DATA_TEXT, DATA_NUM
0xFF (11111111): DATA_FLT8, DATA_FLT4, DATA_DATE4, DATA_DATE8, DATA_MNY4, DATA_MNY8, DATA_VBIN, DATA_BIN
0xFF (11111111): DATA_VCHAR, DATA_CHAR, DATA_BIT, DATA_INT4, DATA_INT2, DATA_INT1, REQ_PARAM, REQ_MSG
0xE6 (11100110): REQ_DYNF, REQ_CURSOR, REQ_BCP, (REQ_MSTMT), (REQ_EVT), REQ_RPC, REQ_LANG, (NONE)
Type [1]: CAP_RESPONSE
Mask [12]:
0x00 (00000000): (RES_NODATA_XML), (NO_SRVPKTSIZE), (RES_NOBLOB_NCHAR_16), (RES_NOLARGEIDENT)
0x00 (00000000): (DATA_NOSINT1), (DATA_NOUNITEXT), (DATA_NOINTERVAL), (DATA_NOTIME), (DATA_NODATE), (BLOB_NONCHAR_SCSU), (BLOB_NONCHAR_8),
(BLOB_NONCHAR_16)
0x00 (00000000): (IMAGE_NONCHAR), (DATA_NONLBIN), (NO_WIDETABLES), (DATA_NOUINTN), (DATA_NOUINT8), (DATA_NOUINT4), (DATA_NOUINT2), (RES_RESERVED1)
0x02 (00000010): (OBJECT_NOBINARY), (DATA_NOCOLUMNSTATUS), (OBJECT_NOCHAR), (OBJECT_NOJAVA1), (DATA_NOINT8), (RES_NOSTRIPBLANKS), RES_NOTDSDEBUG,
(DATA_NOBOUNDARY)
0x00 (00000000): (DATA_NOSENSITIVITY), (PROTO_NOBULK), (PROTO_NOTEXT), (CON_NOINBAND), (CON_NOOOB), (DATA_NOMONEYN), (DATA_NODATETIMEN),
(DATA_NOINTN)
0x00 (00000000): (DATA_NOLBIN), (DATA_NOLCHAR), (DATA_NODEC), (DATA_NOIMAGE), (DATA_NOTEXT), (DATA_NONUM), (DATA_NOFLT8), (DATA_NOFLT4)
0x00 (00000000): (DATA_NODATE4), (DATA_NODATE8), (DATA_NOMNY4), (DATA_NOMNY8), (DATA_NOVBIN), (DATA_NOBIN), (DATA_NOVCHAR), (DATA_NOCHAR)
0x00 (00000000): (DATA_NOBIT), (DATA_NOINT4), (DATA_NOINT2), (DATA_NOINT1), (RES_NOPARAM), (RES_NOEED), (RES_NOMSG), (NONE)
Group
Annual Conference, 2015
Capabilities
CAPABILITY Token (0xE2); variable length.
Length [2]: 28
Type [1]: CAP_REQUEST
Mask [12]:
0x00 (00000000): (MULTI_REQUESTS), (REQ_MIGRATE), (REQ_UNUSED)
0xE9 (11101001): REQ_DBRPC2, REQ_CURINFO3, DATA_XML, (REQ_BLOB_NCHAR_16), REQ_LARGEIDENT, (DATA_SINT1), (CAP_CLUSTERFAILOVER), DATA_UNITEXT
0xB5 (10110101): REQ_SRVPKTSIZE, (CSR_KEYSETDRIVEN), CSR_SEMISENSITIVE, CSR_INSENSITIVE, (CSR_SENSITIVE), CSR_SCROLL, (DATA_INTERVAL), DATA_TIME
0x87 (10000111): DATA_DATE, (BLOB_NCHAR_SCSU), (BLOB_NCHAR_8), (BLOB_NCHAR_16), (IMAGE_NCHAR), DATA_NLBIN, CUR_IMPLICIT, DATA_UINTN
0xE8 (11101000): DATA_UINT8, DATA_UINT4, DATA_UINT2, (REQ_RESERVED2), WIDETABLE, (DATA_COLUMNSTATUS), (OBJECT_BINARY), (REQ_RESERVED1)
0x3B (00111011): (OBJECT_CHAR), (OBJECT_JAVA1), DOL_BULK, DATA_VOID, DATA_INT8, (DATA_BITN), DATA_FLTN, PROTO_DYNPROC
0x61 (01100001): (PROTO_DYNAMIC), DATA_BOUNDARY, DATA_SENSITIVITY, (REQ_URGEVT), (PROTO_BULK), (PROTO_TEXT), (CON_LOGICAL), CON_INBAND
0x7F (01111111): (CON_OOB), CSR_MULTI, CSR_REL, CSR_ABS, CSR_LAST, CSR_FIRST, CSR_PREV, DATA_MONEYN
0xFF (11111111): DATA_DATETIMEN, DATA_INTN, DATA_LBIN, DATA_LCHAR, DATA_DEC, DATA_IMAGE, DATA_TEXT, DATA_NUM
0xFF (11111111): DATA_FLT8, DATA_FLT4, DATA_DATE4, DATA_DATE8, DATA_MNY4, DATA_MNY8, DATA_VBIN, DATA_BIN
0xFF (11111111): DATA_VCHAR, DATA_CHAR, DATA_BIT, DATA_INT4, DATA_INT2, DATA_INT1, REQ_PARAM, REQ_MSG
0xE6 (11100110): REQ_DYNF, REQ_CURSOR, REQ_BCP, (REQ_MSTMT), (REQ_EVT), REQ_RPC, REQ_LANG, (NONE)
Type [1]: CAP_RESPONSE
Mask [12]:
0x00 (00000000): (RES_NODATA_XML), (NO_SRVPKTSIZE), (RES_NOBLOB_NCHAR_16), (RES_NOLARGEIDENT)
0x00 (00000000): (DATA_NOSINT1), (DATA_NOUNITEXT), (DATA_NOINTERVAL), (DATA_NOTIME), (DATA_NODATE), (BLOB_NONCHAR_SCSU), (BLOB_NONCHAR_8),
(BLOB_NONCHAR_16)
0x00 (00000000): (IMAGE_NONCHAR), (DATA_NONLBIN), (NO_WIDETABLES), (DATA_NOUINTN), (DATA_NOUINT8), (DATA_NOUINT4), (DATA_NOUINT2), (RES_RESERVED1)
0x02 (00000010): (OBJECT_NOBINARY), (DATA_NOCOLUMNSTATUS), (OBJECT_NOCHAR), (OBJECT_NOJAVA1), (DATA_NOINT8), (RES_NOSTRIPBLANKS), RES_NOTDSDEBUG,
(DATA_NOBOUNDARY)
0x00 (00000000): (DATA_NOSENSITIVITY), (PROTO_NOBULK), (PROTO_NOTEXT), (CON_NOINBAND), (CON_NOOOB), (DATA_NOMONEYN), (DATA_NODATETIMEN),
(DATA_NOINTN)
0x00 (00000000): (DATA_NOLBIN), (DATA_NOLCHAR), (DATA_NODEC), (DATA_NOIMAGE), (DATA_NOTEXT), (DATA_NONUM), (DATA_NOFLT8), (DATA_NOFLT4)
0x00 (00000000): (DATA_NODATE4), (DATA_NODATE8), (DATA_NOMNY4), (DATA_NOMNY8), (DATA_NOVBIN), (DATA_NOBIN), (DATA_NOVCHAR), (DATA_NOCHAR)
0x00 (00000000): (DATA_NOBIT), (DATA_NOINT4), (DATA_NOINT2), (DATA_NOINT1), (RES_NOPARAM), (RES_NOEED), (RES_NOMSG), (NONE)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
GuiProx
•Java gateway similar to Ribo in ways
•Internal tool and provided to customers under specific
circumstances
•Raw TDS output
•Contains response and “think” times
•Timestamps
•Adjustable log size
Group
Annual Conference, 2015
GuiProx
•Java gateway similar to Ribo in ways
•Internal tool and provided to customers under specific
circumstances
•Raw TDS output
•Contains response and “think” times
•Timestamps
•Adjustable log size
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
GuiProx
[2014-03-10 12:48:52.124] Listening on port : 6001
[2014-03-10 12:48:52.124] Server host is : 10.168.131.204
[2014-03-10 12:48:52.124] Server port is : 1570
[2014-03-10 12:48:52.124] Hex dump filter : true
[2014-03-10 12:48:52.124] Sql filter : true
[2014-03-10 12:48:52.124] TimeStamp entries : true
[2014-03-10 12:48:52.134] Lilo dump filter : true
[2014-03-10 12:48:52.134] Gui display window : false
[2014-03-10 12:48:52.134] SSL Debugging : false
[2014-03-10 12:48:52.134] Capture Tds : true
[2014-03-10 12:48:52.134] Log Sql to file : false
[2014-03-10 12:48:52.134] **** _sendDelay set to 0 millisecs
[2014-03-10 12:48:52.134] **** _recvDelay set to 0 millisecs
[2014-03-10 12:48:52.134] **** _cancelDelay set to false
[2014-03-10 12:48:54.764] Thread-4: Opening socket to Server
[2014-03-10 12:48:54.764] Thread-4: Tds Logging to file : cap0.tds
Group
Annual Conference, 2015
GuiProx
[2014-03-10 12:48:52.124] Listening on port : 6001
[2014-03-10 12:48:52.124] Server host is : 10.168.131.204
[2014-03-10 12:48:52.124] Server port is : 1570
[2014-03-10 12:48:52.124] Hex dump filter : true
[2014-03-10 12:48:52.124] Sql filter : true
[2014-03-10 12:48:52.124] TimeStamp entries : true
[2014-03-10 12:48:52.134] Lilo dump filter : true
[2014-03-10 12:48:52.134] Gui display window : false
[2014-03-10 12:48:52.134] SSL Debugging : false
[2014-03-10 12:48:52.134] Capture Tds : true
[2014-03-10 12:48:52.134] Log Sql to file : false
[2014-03-10 12:48:52.134] **** _sendDelay set to 0 millisecs
[2014-03-10 12:48:52.134] **** _recvDelay set to 0 millisecs
[2014-03-10 12:48:52.134] **** _cancelDelay set to false
[2014-03-10 12:48:54.764] Thread-4: Opening socket to Server
[2014-03-10 12:48:54.764] Thread-4: Tds Logging to file : cap0.tds
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
GuiProx
[2014-03-10 12:48:54.764] Thread-4: Client->Server: read 512 from client
[2014-03-10 12:48:54.764]
00000000: 02 00 02 00 00 00 00 00 42 4a 43 4e 30 30 35 32 ........BJCN0052
00000010: 36 31 32 37 41 00 00 00 00 00 00 00 00 00 00 00 6127A...........
00000020: 00 00 00 00 00 00 0d 73 61 00 00 00 00 00 00 00 .......sa.......
00000030: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000040: 00 00 00 00 00 02 73 65 63 72 65 74 00 00 00 00 ......secret....
00000050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000060: 00 00 00 00 06 35 39 32 00 00 00 00 00 00 00 00 .....592........
00000070: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000080: 00 00 00 03 03 01 06 0a 09 01 01 00 00 00 00 00 ................
00000090: 00 00 00 00 69 73 71 6c 00 00 00 00 00 00 00 00 ....isql........
000000a0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
000000b0: 00 00 04 67 75 69 70 72 6f 78 00 00 00 00 00 00 ...guiprox......
000000c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
000000d0: 00 07 00 06 73 65 63 72 65 74 00 00 00 00 00 00 ....secret......
000000e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
*
000001d0: 00 08 05 00 00 00 43 54 2d 4c 69 62 72 61 72 79 ......CT-Library
000001e0: 0a 0f 07 00 0c 00 0d 11 00 73 5f 65 6e 67 6c 69 .........s_engli
000001f0: 73 68 00 00 00 00 00 00 00 00 00 00 00 00 00 00 sh..............
[2014-03-10 12:48:54.764] Thread-4: Client->Server: read 107 from client
[2014-03-10 12:48:54.774]
00000000: 02 01 00 6b 00 00 00 00 00 00 00 00 00 00 00 00 ...k............
00000010: 00 00 00 00 08 00 00 00 00 00 00 00 00 69 73 6f .............iso
00000020: 5f 31 00 00 00 00 00 00 00 00 00 00 00 00 00 00 _1..............
00000030: 00 00 00 00 00 00 00 00 00 00 00 05 00 35 31 32 .............512
00000040: 00 00 00 03 00 00 00 00 e2 20 00 01 0e 01 3c e2 ......... ....<.
00000050: f9 b5 87 ea bb 6d 7f ff ff ff fe 02 0e 00 00 00 .....m..........
00000060: 00 00 18 60 00 00 02 68 00 00 00 ...`...h...
[2014-03-10 12:48:54.774] Thread-5: Server->Client: packetSize set to 2048
[2014-03-10 12:48:54.784] Thread-5: Server->Client: read 166 from server. response time was 9.327
Group
Annual Conference, 2015
GuiProx
[2014-03-10 12:48:54.764] Thread-4: Client->Server: read 512 from client
[2014-03-10 12:48:54.764]
00000000: 02 00 02 00 00 00 00 00 42 4a 43 4e 30 30 35 32 ........BJCN0052
00000010: 36 31 32 37 41 00 00 00 00 00 00 00 00 00 00 00 6127A...........
00000020: 00 00 00 00 00 00 0d 73 61 00 00 00 00 00 00 00 .......sa.......
00000030: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000040: 00 00 00 00 00 02 73 65 63 72 65 74 00 00 00 00 ......secret....
00000050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000060: 00 00 00 00 06 35 39 32 00 00 00 00 00 00 00 00 .....592........
00000070: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
00000080: 00 00 00 03 03 01 06 0a 09 01 01 00 00 00 00 00 ................
00000090: 00 00 00 00 69 73 71 6c 00 00 00 00 00 00 00 00 ....isql........
000000a0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
000000b0: 00 00 04 67 75 69 70 72 6f 78 00 00 00 00 00 00 ...guiprox......
000000c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
000000d0: 00 07 00 06 73 65 63 72 65 74 00 00 00 00 00 00 ....secret......
000000e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
*
000001d0: 00 08 05 00 00 00 43 54 2d 4c 69 62 72 61 72 79 ......CT-Library
000001e0: 0a 0f 07 00 0c 00 0d 11 00 73 5f 65 6e 67 6c 69 .........s_engli
000001f0: 73 68 00 00 00 00 00 00 00 00 00 00 00 00 00 00 sh..............
[2014-03-10 12:48:54.764] Thread-4: Client->Server: read 107 from client
[2014-03-10 12:48:54.774]
00000000: 02 01 00 6b 00 00 00 00 00 00 00 00 00 00 00 00 ...k............
00000010: 00 00 00 00 08 00 00 00 00 00 00 00 00 69 73 6f .............iso
00000020: 5f 31 00 00 00 00 00 00 00 00 00 00 00 00 00 00 _1..............
00000030: 00 00 00 00 00 00 00 00 00 00 00 05 00 35 31 32 .............512
00000040: 00 00 00 03 00 00 00 00 e2 20 00 01 0e 01 3c e2 ......... ....<.
00000050: f9 b5 87 ea bb 6d 7f ff ff ff fe 02 0e 00 00 00 .....m..........
00000060: 00 00 18 60 00 00 02 68 00 00 00 ...`...h...
[2014-03-10 12:48:54.774] Thread-5: Server->Client: packetSize set to 2048
[2014-03-10 12:48:54.784] Thread-5: Server->Client: read 166 from server. response time was 9.327
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Network Sniffers
•Most operating systems contain built-in sniffers
•tcpdump (Linux)
•snoop (Solaris)
•Wireshark very popular and used to view captures
from other sniffer apps
•Learn TCP/IP to understand the packets
Group
Annual Conference, 2015
Network Sniffers
•Most operating systems contain built-in sniffers
•tcpdump (Linux)
•snoop (Solaris)
•Wireshark very popular and used to view captures
from other sniffer apps
•Learn TCP/IP to understand the packets
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The srv.log from Open Server
May 30 18:01:01 2013: Sybase Server-Library/15.7/P-EBF21007-21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:34:18 2013May
30 18:01:01 2013: Sybase Client-Library/15.7/P-EBF21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:32:57 2013
spid 6 - read: login, not eom, len=512, chan=0, pack=0, win=0, client
0x424a 0x434e 0x3030 0x3532 0x3631 0x3237 0x4100 0x0000 | BJCN00526127A
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0d73 | s
0x6100 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | a
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0002 0x7365 | se
0x6372 0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | cret
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0631 0x3033 | 103
0x3532 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 52
0x0000 0x0000 0x0000 0x0000 0x0000 0x0005 0x0301 0x060a |
0x0901 0x0100 0x0000 0x0000 0x0000 0x0000 0x6973 0x716c | isql
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0463 0x746f 0x7331 | ctos1
0x3537 0x3000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 570
0x0000 0x0000 0x0000 0x0000 0x0008 0x0006 0x7365 0x6372 | secr
0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | et
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0008 0x0500 0x0000 0x4354 | CT
0x2d4c 0x6962 0x7261 0x7279 0x0a0f 0x0700 0x0a00 0x0d11 | -Library
0x0073 0x5f65 0x6e67 0x6c69 0x7368 0x0000 0x0000 0x0000 | s_english
0x0000 0x0000 0x0000 0x0000 0x.... 0x.... 0x.... 0x.... |
===========================================================================
Group
Annual Conference, 2015
The srv.log from Open Server
May 30 18:01:01 2013: Sybase Server-Library/15.7/P-EBF21007-21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:34:18 2013May
30 18:01:01 2013: Sybase Client-Library/15.7/P-EBF21006 SP100/X64/BUILD1570-026/OPT/Sun Mar 24 08:32:57 2013
spid 6 - read: login, not eom, len=512, chan=0, pack=0, win=0, client
0x424a 0x434e 0x3030 0x3532 0x3631 0x3237 0x4100 0x0000 | BJCN00526127A
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0d73 | s
0x6100 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | a
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0002 0x7365 | se
0x6372 0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | cret
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0631 0x3033 | 103
0x3532 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 52
0x0000 0x0000 0x0000 0x0000 0x0000 0x0005 0x0301 0x060a |
0x0901 0x0100 0x0000 0x0000 0x0000 0x0000 0x6973 0x716c | isql
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0463 0x746f 0x7331 | ctos1
0x3537 0x3000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | 570
0x0000 0x0000 0x0000 0x0000 0x0008 0x0006 0x7365 0x6372 | secr
0x6574 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 | et
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 0x0000 |
0x0000 0x0000 0x0000 0x0000 0x0008 0x0500 0x0000 0x4354 | CT
0x2d4c 0x6962 0x7261 0x7279 0x0a0f 0x0700 0x0a00 0x0d11 | -Library
0x0073 0x5f65 0x6e67 0x6c69 0x7368 0x0000 0x0000 0x0000 | s_english
0x0000 0x0000 0x0000 0x0000 0x.... 0x.... 0x.... 0x.... |
===========================================================================
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Open Client debug variable
C:\...\Atlanta\TDS>set SYBOCS_DEBUG_FLAGS=CS_DBG_PROTOCOL
C:\...\Atlanta\TDS>isql -Usa -Psecret -Spvw71572 -Dodbc
1> select * from tds_table
2> go
c1 c2 c3 c4
----------- -------------------- ------------- -------------------------------
1 TDS_LANGUAGE 2.1000 Mar 8 2015 9:56PM
2 TDS_DBRPC 14.6000 Mar 8 2015 9:56PM
3 TDS_CURDECLARE 8.6100 Mar 8 2015 9:56PM
4 TDS_DYNAMIC 14.7000 Mar 8 2015 9:56PM
5 TDS_ROW 13.1000 Mar 8 2015 9:56PM
(5 rows affected)
1> quit
Directory of C:\...\Atlanta\TDS
03/10/2015 10:44 PM 2,104 capF6BF.tmp
Group
Annual Conference, 2015
Open Client debug variable
C:\...\Atlanta\TDS>set SYBOCS_DEBUG_FLAGS=CS_DBG_PROTOCOL
C:\...\Atlanta\TDS>isql -Usa -Psecret -Spvw71572 -Dodbc
1> select * from tds_table
2> go
c1 c2 c3 c4
----------- -------------------- ------------- -------------------------------
1 TDS_LANGUAGE 2.1000 Mar 8 2015 9:56PM
2 TDS_DBRPC 14.6000 Mar 8 2015 9:56PM
3 TDS_CURDECLARE 8.6100 Mar 8 2015 9:56PM
4 TDS_DYNAMIC 14.7000 Mar 8 2015 9:56PM
5 TDS_ROW 13.1000 Mar 8 2015 9:56PM
(5 rows affected)
1> quit
Directory of C:\...\Atlanta\TDS
03/10/2015 10:44 PM 2,104 capF6BF.tmp
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Open Client debug variable
Run it through RIBO:
You can run the Ribo utility on the tds file to format in text format.
You can use a script:
ribo %1\%2.tds %1\%2.txt
mv or rename the *.tmp file to *.tds
In this case
ribo C:\...\Atlanta\TDS capF6BF.tds
Creates capF6BF.txt file
You can view file in editor to check the TDS
Group
Annual Conference, 2015
Open Client debug variable
Run it through RIBO:
You can run the Ribo utility on the tds file to format in text format.
You can use a script:
ribo %1\%2.tds %1\%2.txt
mv or rename the *.tmp file to *.tds
In this case
ribo C:\...\Atlanta\TDS capF6BF.tds
Creates capF6BF.txt file
You can view file in editor to check the TDS
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Protocol Capture
•Drivers now have built-in TDS trace generator
•Create in same TDS format as RIBO
•Use RIBO to format into Text
•jConnect: PROTOCOL_CAPTURE
•One thread/one connection
•Use RIBO for multi-thread/connections
•ASE ADO.NET and ODBC
•protocolcapture=filename.tds
•Appends pid and thread/connection number
•Starts at 0
Group
Annual Conference, 2015
Protocol Capture
•Drivers now have built-in TDS trace generator
•Create in same TDS format as RIBO
•Use RIBO to format into Text
•jConnect: PROTOCOL_CAPTURE
•One thread/one connection
•Use RIBO for multi-thread/connections
•ASE ADO.NET and ODBC
•protocolcapture=filename.tds
•Appends pid and thread/connection number
•Starts at 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS Tokens
•TDS Tokens are the containers of the activity and
information
•Token expresses what it does
•Language, rpc, cursor, dynamic
•Is it data, message, or something else?
•Data portion contains the information
•Data from server response
•Format information for parameters and fields
•Messages, warnings and errors
•Other forms of status
Group
Annual Conference, 2015
TDS Tokens
•TDS Tokens are the containers of the activity and
information
•Token expresses what it does
•Language, rpc, cursor, dynamic
•Is it data, message, or something else?
•Data portion contains the information
•Data from server response
•Format information for parameters and fields
•Messages, warnings and errors
•Other forms of status
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Environment Change
•TDS_ENVCHANGE (0xE3)
•The token contains environment change information
•Items that can change, based on ASE defaults and client
specified
•Current database
•Charset
•Language
•Packet size
Group
Annual Conference, 2015
Environment Change
•TDS_ENVCHANGE (0xE3)
•The token contains environment change information
•Items that can change, based on ASE defaults and client
specified
•Current database
•Charset
•Language
•Packet size
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Environment Change -
TDS_ENVCHANGE (0xE3)
ENVCHANGE Token (0xE3); variable length.
Length [2]: 13
Type [1]: ENV_DB (0x01)
Length of New Value [1]: 4
New Value [4]: “odbc"
Length of Old Value [1]: 6
Old Value [6]: "master"
Group
Annual Conference, 2015
Environment Change -
TDS_ENVCHANGE (0xE3)
ENVCHANGE Token (0xE3); variable length.
Length [2]: 13
Type [1]: ENV_DB (0x01)
Length of New Value [1]: 4
New Value [4]: “odbc"
Length of Old Value [1]: 6
Old Value [6]: "master"
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Environment Change – use database
exchange
LANGUAGE Token (0x21); variable length.
Length [4]: 10
Status [1]: UNUSED (0x00)
Text Length [0]: [9]
Text [9]: "use odbc
"
Capture Record Header
Source [4]: RESPONSE (0x00000002)
Length [4]: 101
PDU Header
TDS Packet Type [1]: BUF_RESPONSE (0x04)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 101
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
ENVCHANGE Token (0xE3); variable length.
Length [2]: 13
Type [1]: ENV_DB (0x01)
Length of New Value [1]: 4
New Value [4]: "odbc"
Length of Old Value [1]: 6
Old Value [6]: "master"
Group
Annual Conference, 2015
Environment Change – use database
exchange
LANGUAGE Token (0x21); variable length.
Length [4]: 10
Status [1]: UNUSED (0x00)
Text Length [0]: [9]
Text [9]: "use odbc
"
Capture Record Header
Source [4]: RESPONSE (0x00000002)
Length [4]: 101
PDU Header
TDS Packet Type [1]: BUF_RESPONSE (0x04)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 101
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
ENVCHANGE Token (0xE3); variable length.
Length [2]: 13
Type [1]: ENV_DB (0x01)
Length of New Value [1]: 4
New Value [4]: "odbc"
Length of Old Value [1]: 6
Old Value [6]: "master"
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Environment Change – use database
exchange
EED Token (0xE5); variable length.
Length [2]: 65
Message Number [4]: 5701
Message State [1]: 1
Message Class [1]: 10
SQL State Length [1]: 5
SQL State [5]: "ZZZZZ"
Status [1]: NO_EED (0x00)
Transaction State [2]: TDS_TRAN_SUCCEED (0x0001)
Message Length [2]: 36
Message Text [36]: "Changed database context to 'odbc'.
"
Server Name Length [1]: 8
Server Name [8]: "pvxp1253"
Stored Proc. Name Length [1]: 0
Line Number [2]: 1
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL (0x0000)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count (unused) [4]: 0
Group
Annual Conference, 2015
Environment Change – use database
exchange
EED Token (0xE5); variable length.
Length [2]: 65
Message Number [4]: 5701
Message State [1]: 1
Message Class [1]: 10
SQL State Length [1]: 5
SQL State [5]: "ZZZZZ"
Status [1]: NO_EED (0x00)
Transaction State [2]: TDS_TRAN_SUCCEED (0x0001)
Message Length [2]: 36
Message Text [36]: "Changed database context to 'odbc'.
"
Server Name Length [1]: 8
Server Name [8]: "pvxp1253"
Stored Proc. Name Length [1]: 0
Line Number [2]: 1
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL (0x0000)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count (unused) [4]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_LANGUAGE
•Sends SQL statement to the ASE as a
language command
•Think of the command as what you’d send
via ISQL
•SELECT * FROM authors
•INSERT INTO table1 values (1, ‘a’, 12.3)
•Simple token
•Length [4 byte unsigned] – length limited to
0xFFFF
•65535 bytes
•Status
•Language Text
Group
Annual Conference, 2015
TDS_LANGUAGE
•Sends SQL statement to the ASE as a
language command
•Think of the command as what you’d send
via ISQL
•SELECT * FROM authors
•INSERT INTO table1 values (1, ‘a’, 12.3)
•Simple token
•Length [4 byte unsigned] – length limited to
0xFFFF
•65535 bytes
•Status
•Language Text
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_LANGUAGE
Here’s the sample “select @@version”
LANGUAGE Token (0x21); variable length.
Length [4]: 18
Status [1]: UNUSED (0x00)
Text Length [0]: [17]
Text [17]: "select
@@version
"
Group
Annual Conference, 2015
TDS_LANGUAGE
Here’s the sample “select @@version”
LANGUAGE Token (0x21); variable length.
Length [4]: 18
Status [1]: UNUSED (0x00)
Text Length [0]: [17]
Text [17]: "select
@@version
"
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response to TDS_LANGUAGE
Remember the PDU header, and this is a response:
Capture Record Header
Source [4]: RESPONSE (0x00000002)
Length [4]: 163
PDU Header
TDS Packet Type [1]: BUF_RESPONSE (0x04)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 163
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
Group
Annual Conference, 2015
Response to TDS_LANGUAGE
Remember the PDU header, and this is a response:
Capture Record Header
Source [4]: RESPONSE (0x00000002)
Length [4]: 163
PDU Header
TDS Packet Type [1]: BUF_RESPONSE (0x04)
Status [1]: BUFSTAT_EOM (0x01)
Length [2]: 163
Channel [2]: 0
Packet No. [1]: 0
Window [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
Group
Annual Conference, 2015
Response to TDS_LANGUAGE: Row
format
This is the TDS_ROWFMT2 token
Describes data type, length, and status of row
data
ROWFMT2 Token (0x61);
Length [4]: 18
Number of Columns [2]: 1
Column 1
Column Label Length [1]: 0
Catalog Name Length [1]: 0
Schema Length [1]: 0
Table Name Length [1]: 0
Column Name Length [1]: 0
Status [4]: ROW_UPDATABLE + ROW_NULLALLOWED (0x00000030)
User Type [4]: 0x00000002
Data Type [1]: VARCHAR
Length [1]: 255
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_ROWFMT2 Data Type
The TDS Datatype section describes the fields for the TDS
datatype in the format tokens:
Group
Annual Conference, 2015
TDS_ROWFMT2 Data Type
The TDS Datatype section describes the fields for the TDS
datatype in the format tokens:
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_ROW: The data
The TDS_ROW, 0xD1, token represents a row of data
ROW Token (0xD1); variable length.
Column 1
Length [1]: 117
Row data [117]: "Adaptive Server
Enterprise/12.5.3/EBF 13331 ESD#7/P/NT (IX86)/OS
4.0/ase1253/1951/32-bit/OPT/Fri Mar 24 02:17:56 2006"
(0x41646170746976652053657276657220456E74657270726973652F3
1322E352E332F4542462031333333312045534423372F502F4E5420284
9583836292F4F5320342E302F617365313235332F313935312F33322D6
269742F4F50542F467269204D61722032342030323A31373A353620323
03036)
Group
Annual Conference, 2015
TDS_ROW: The data
The TDS_ROW, 0xD1, token represents a row of data
ROW Token (0xD1); variable length.
Column 1
Length [1]: 117
Row data [117]: "Adaptive Server
Enterprise/12.5.3/EBF 13331 ESD#7/P/NT (IX86)/OS
4.0/ase1253/1951/32-bit/OPT/Fri Mar 24 02:17:56 2006"
(0x41646170746976652053657276657220456E74657270726973652F3
1322E352E332F4542462031333333312045534423372F502F4E5420284
9583836292F4F5320342E302F617365313235332F313935312F33322D6
269742F4F50542F467269204D61722032342030323A31373A353620323
03036)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_DONE – When things are done
•The TDS_DONE token indicates completion status of the
response
•One for each statement
•On selects it returns rowcount
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_COUNT (0x0010)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count [4]: 1
Group
Annual Conference, 2015
TDS_DONE – When things are done
•The TDS_DONE token indicates completion status of the
response
•One for each statement
•On selects it returns rowcount
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_COUNT (0x0010)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count [4]: 1
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Another select example
This example selects four columns:
create table tds_table
(
c1 int not null,
c2 varchar(20),
c3 numeric(10,4),
c4 datetime,
constraint tds_pk primary key (c1)
)
Group
Annual Conference, 2015
Another select example
This example selects four columns:
create table tds_table
(
c1 int not null,
c2 varchar(20),
c3 numeric(10,4),
c4 datetime,
constraint tds_pk primary key (c1)
)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
ODBC Connection to select
You can use the login packet to identify the application and
the client
Login Record; fixed length.
Host Name [30]: "PVEROXP"
Host Name Length [1]: 7
User Name [30]: "sa"
User Name Length [1]: 2
Password [30]: "sybase"
Password Length [1]: 6
Host Process [30]: "5580"
Host Process Length [1]: 4
Application Name [30]: "ODBCTEST"
Application Name Length [1]: 8
Service Name [30]: ""
Service Name Length [1]: 0
Remote Passwords [255]: <universal>/"sybase"
Remote Passwords Length [1]: 8
TDS Version [4]: 5.0.0.0
Prog Name [30]: "tdsconn"
Prog Name Length [1]: 7
Prog Version [4]: 12.0.5.0
Convert Shorts [1]: 0
4-byte Float Format [1]: 13
4-byte Date Format [1]: 17
Language [30]: "us_english"
Language Length [1]: 10
Group
Annual Conference, 2015
ODBC Connection to select
You can use the login packet to identify the application and
the client
Login Record; fixed length.
Host Name [30]: "PVEROXP"
Host Name Length [1]: 7
User Name [30]: "sa"
User Name Length [1]: 2
Password [30]: "sybase"
Password Length [1]: 6
Host Process [30]: "5580"
Host Process Length [1]: 4
Application Name [30]: "ODBCTEST"
Application Name Length [1]: 8
Service Name [30]: ""
Service Name Length [1]: 0
Remote Passwords [255]: <universal>/"sybase"
Remote Passwords Length [1]: 8
TDS Version [4]: 5.0.0.0
Prog Name [30]: "tdsconn"
Prog Name Length [1]: 7
Prog Version [4]: 12.0.5.0
Convert Shorts [1]: 0
4-byte Float Format [1]: 13
4-byte Date Format [1]: 17
Language [30]: "us_english"
Language Length [1]: 10
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Getting spid from TDS_DONE
response to TDS_LOGIN
•On newer ASE servers [verify the earliest] the
TDS_DONE response to Login request contains the
spid of the session
•Excellent way to match the TDS trace with the ASE
session for trouble-shooting purposes
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL (0x0000)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count (unused) [4]: 18
Group
Annual Conference, 2015
Getting spid from TDS_DONE
response to TDS_LOGIN
•On newer ASE servers [verify the earliest] the
TDS_DONE response to Login request contains the
spid of the session
•Excellent way to match the TDS trace with the ASE
session for trouble-shooting purposes
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL (0x0000)
TranState [2]: TDS_TRAN_PROGRESS (0x0002)
Count (unused) [4]: 18
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The TDS_LANGUAGE
The select is executed via the TDS_LANGUAGE event
When ODBCTEST calls SQLExecDirect(“SELECT * FROM
tds_table”)
LANGUAGE Token (0x21); variable length.
Length [4]: 24
Status [1]: UNUSED (0x00)
Text Length [0]: [23]
Text [23]: "select * from tds_table"
Group
Annual Conference, 2015
The TDS_LANGUAGE
The select is executed via the TDS_LANGUAGE event
When ODBCTEST calls SQLExecDirect(“SELECT * FROM
tds_table”)
LANGUAGE Token (0x21); variable length.
Length [4]: 24
Status [1]: UNUSED (0x00)
Text Length [0]: [23]
Text [23]: "select * from tds_table"
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Formats
•Row formats are provided for row results and contain
description information for the specified column
•TDS tokens TDS_ROWFMT and TDS_ROWFMT2
provide this information.
•Used by applications when binding the fields to
program variables
•The same concept occurs with parameters
•TDS_PARAMFMT/PARAMFMT2 provide parameter
information
Group
Annual Conference, 2015
Formats
•Row formats are provided for row results and contain
description information for the specified column
•TDS tokens TDS_ROWFMT and TDS_ROWFMT2
provide this information.
•Used by applications when binding the fields to
program variables
•The same concept occurs with parameters
•TDS_PARAMFMT/PARAMFMT2 provide parameter
information
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_ROWFMT2 exposed
ROWFMT2 Token (0x61);
Length [4]: 139
Number of Columns [2]: 4
Column 1
Column Label Length [1]: 2
Column Label [2]: "c1"
Catalog Name Length [1]: 4
Catalog [4]: "odbc"
Schema Length [1]: 3
Scehma [3]: "dbo"
Table Name Length [1]: 9
Table Name [9]: "tds_table"
Column Name Length [1]: 2
Column Name [2]: "c1"
Status [4]: ROW_UPDATABLE (0x00000010)
User Type [4]: 0x00000007
Data Type [1]: INT4
Length [0]: [4]
Locale Length [1]: 0
Group
Annual Conference, 2015
TDS_ROWFMT2 exposed
ROWFMT2 Token (0x61);
Length [4]: 139
Number of Columns [2]: 4
Column 1
Column Label Length [1]: 2
Column Label [2]: "c1"
Catalog Name Length [1]: 4
Catalog [4]: "odbc"
Schema Length [1]: 3
Scehma [3]: "dbo"
Table Name Length [1]: 9
Table Name [9]: "tds_table"
Column Name Length [1]: 2
Column Name [2]: "c1"
Status [4]: ROW_UPDATABLE (0x00000010)
User Type [4]: 0x00000007
Data Type [1]: INT4
Length [0]: [4]
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_PARAMFMT exposed
LANGUAGE Token (0x21); variable length.
Length [4]: 56
Status [1]: PARAMETERIZED
(0x01)
Text Length [0]: [55]
Text [55]: "select * from
tds_table where c1=@dr_ta0 and c2=@dr_ta1"
Group
Annual Conference, 2015
TDS_PARAMFMT exposed
LANGUAGE Token (0x21); variable length.
Length [4]: 56
Status [1]: PARAMETERIZED
(0x01)
Text Length [0]: [55]
Text [55]: "select * from
tds_table where c1=@dr_ta0 and c2=@dr_ta1"
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
TDS_PARAMFMT exposed
PARAMFMT Token (0xEC); variable length.
Length [2]: 34
Number of Params [2]: 2
Param 1
Name Length [1]: 7
Name [7]: "@dr_ta0"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: INTN
Length [1]: 4
Locale Length [1]: 0
Param 2
Name Length [1]: 7
Name [7]: "@dr_ta1"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: CHAR
Length [1]: 255
Locale Length [1]: 0
Group
Annual Conference, 2015
TDS_PARAMFMT exposed
PARAMFMT Token (0xEC); variable length.
Length [2]: 34
Number of Params [2]: 2
Param 1
Name Length [1]: 7
Name [7]: "@dr_ta0"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: INTN
Length [1]: 4
Locale Length [1]: 0
Param 2
Name Length [1]: 7
Name [7]: "@dr_ta1"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: CHAR
Length [1]: 255
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats
1> select * from tds_table
2> go
c1 c2 c3 c4
----------- -------------------- ------------- -------------------------------
1 TDS_LANGUAGE 2.1000 Mar 8 2015 9:56PM
2 TDS_DBRPC 14.6000 Mar 8 2015 9:56PM
3 TDS_CURDECLARE 8.6100 Mar 8 2015 9:56PM
4 TDS_DYNAMIC 14.7000 Mar 8 2015 9:56PM
5 TDS_ROW 13.1000 Mar 8 2015 9:56PM
Group
Annual Conference, 2015
The tds_table row formats
1> select * from tds_table
2> go
c1 c2 c3 c4
----------- -------------------- ------------- -------------------------------
1 TDS_LANGUAGE 2.1000 Mar 8 2015 9:56PM
2 TDS_DBRPC 14.6000 Mar 8 2015 9:56PM
3 TDS_CURDECLARE 8.6100 Mar 8 2015 9:56PM
4 TDS_DYNAMIC 14.7000 Mar 8 2015 9:56PM
5 TDS_ROW 13.1000 Mar 8 2015 9:56PM
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats
ROWFMT Token (0xEE);
Length [2]: 46
Number of Columns [2]: 4
Column 1
Name Length [1]: 2
Name [2]: "c1"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 7
Data Type [1]: INT4
Length [0]: [4]
Locale Length [1]: 0
Group
Annual Conference, 2015
The tds_table row formats
ROWFMT Token (0xEE);
Length [2]: 46
Number of Columns [2]: 4
Column 1
Name Length [1]: 2
Name [2]: "c1"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 7
Data Type [1]: INT4
Length [0]: [4]
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats
Column 2
Name Length [1]: 2
Name [2]: "c2"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 2
Data Type [1]: VARCHAR
Length [1]: 20
Locale Length [1]: 00
Group
Annual Conference, 2015
The tds_table row formats
Column 2
Name Length [1]: 2
Name [2]: "c2"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 2
Data Type [1]: VARCHAR
Length [1]: 20
Locale Length [1]: 00
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats
Column 3
Name Length [1]: 2
Name [2]: "c3"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 10
Data Type [1]: NUMN
Length [1]: 6
Precision [1]: 10
Scale [1]: 4
Locale Length [1]: 0
Group
Annual Conference, 2015
The tds_table row formats
Column 3
Name Length [1]: 2
Name [2]: "c3"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 10
Data Type [1]: NUMN
Length [1]: 6
Precision [1]: 10
Scale [1]: 4
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats
Column 4
Name Length [1]: 2
Name [2]: "c4"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 12
Data Type [1]: DATETIM
Length [0]: [8]
Locale Length [1]: 0
Group
Annual Conference, 2015
The tds_table row formats
Column 4
Name Length [1]: 2
Name [2]: "c4"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 12
Data Type [1]: DATETIM
Length [0]: [8]
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The tds_table row formats: Results
ROW Token (0xD1); variable length.
Column 1
Length [0]: [4]
Row data [4]: 1 (0x00000001)
Column 2
Length [1]: 12
Row data [12]: "TDS_LANGUAGE" (0x5444535F4C414E4755414745)
Column 3
Length [1]: 6
Row data [6]: 2.1000
Column 4
Length [0]: [8]
Row data [8]: 2015-03-08 21:56:51.533
Group
Annual Conference, 2015
The tds_table row formats: Results
ROW Token (0xD1); variable length.
Column 1
Length [0]: [4]
Row data [4]: 1 (0x00000001)
Column 2
Length [1]: 12
Row data [12]: "TDS_LANGUAGE" (0x5444535F4C414E4755414745)
Column 3
Length [1]: 6
Row data [6]: 2.1000
Column 4
Length [0]: [8]
Row data [8]: 2015-03-08 21:56:51.533
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
Group
Annual Conference, 2015
Contents
Introduction to TDS
Versions
PDF documents
TDS Documentation
Capture tools
Ribo
GuiProx
Sniffers
Srv.log
Tokens and Flow
TDS_ENVCHANGE, EED, LANGUAGE, etc
Simple select from execution to response, result set
Formats
Row Data
Parameters
Stored procedure calls with parameters
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The sp_tds_proc procedure
create procedure sp_tds_proc (
@p1 int,
@p2 varchar(50),
@p3 varchar(50) output
)
as
select c3, c4 from tds_table where c1= @p1 and c2 = @p2
select @p3 = ‘Sent from sp_tds_proc’
return 0
go
Group
Annual Conference, 2015
The sp_tds_proc procedure
create procedure sp_tds_proc (
@p1 int,
@p2 varchar(50),
@p3 varchar(50) output
)
as
select c3, c4 from tds_table where c1= @p1 and c2 = @p2
select @p3 = ‘Sent from sp_tds_proc’
return 0
go
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The sp_tds_proc procedure
-Cover four concepts of stored procedures
-Input/Output parameters
-Return status
-Formats (similar to Row Results)
-Result set processing
Group
Annual Conference, 2015
The sp_tds_proc procedure
-Cover four concepts of stored procedures
-Input/Output parameters
-Return status
-Formats (similar to Row Results)
-Result set processing
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
The sp_tds_proc
procedure
Parameter: 3 Value: null
Parameter buffer size: 4 chars
Enter data type :char
Enter parameter name with '@':@p3
input or output parameter?:output
target_format.status : 1024
Check for null
ct_results : retcode : SUCCEED
TYPE: ROW_RESULT
----------Row 1----------
c3 : 2.1000
c4 : Mar 8 2015 9:56PM
ct_results : retcode : SUCCEED
TYPE: CMD_DONE
(1 rows affected)
ct_results : retcode : SUCCEED
TYPE: STATUS_RESULT
----------Row 1----------
Return Status : 0
ct_results : retcode : SUCCEED
TYPE: PARAM_RESULT
----------Row 1----------
@p3 : Sent from sp_tds_proc
ct_results : retcode : SUCCEED
TYPE: CMD_DONE
(1 rows affected)
ct_results : retcode : END_RESULTS
Run it through sqltest
Enter the values:
1> sp_tds_proc
1,'TDS_LANGUAGE',null
Parameter: 1 Value: 1
Parameter buffer size: 1 chars
Enter data:int
Enter parameter name with '@':@p1
input or output parameter?:input
target_format.status : 256
current_token is 1
current_token length is 1
Parameter: 2 Value: 'TDS_LANGUAGE'
Parameter buffer size: 14 chars
Enter data type :char
Enter parameter name with '@':@p2
input or output parameter?:input
target_format.status : 256
Check for null
Group
Annual Conference, 2015
The sp_tds_proc
procedure
Parameter: 3 Value: null
Parameter buffer size: 4 chars
Enter data type :char
Enter parameter name with '@':@p3
input or output parameter?:output
target_format.status : 1024
Check for null
ct_results : retcode : SUCCEED
TYPE: ROW_RESULT
----------Row 1----------
c3 : 2.1000
c4 : Mar 8 2015 9:56PM
ct_results : retcode : SUCCEED
TYPE: CMD_DONE
(1 rows affected)
ct_results : retcode : SUCCEED
TYPE: STATUS_RESULT
----------Row 1----------
Return Status : 0
ct_results : retcode : SUCCEED
TYPE: PARAM_RESULT
----------Row 1----------
@p3 : Sent from sp_tds_proc
ct_results : retcode : SUCCEED
TYPE: CMD_DONE
(1 rows affected)
ct_results : retcode : END_RESULTS
Run it through sqltest
Enter the values:
1> sp_tds_proc
1,'TDS_LANGUAGE',null
Parameter: 1 Value: 1
Parameter buffer size: 1 chars
Enter data:int
Enter parameter name with '@':@p1
input or output parameter?:input
target_format.status : 256
current_token is 1
current_token length is 1
Parameter: 2 Value: 'TDS_LANGUAGE'
Parameter buffer size: 14 chars
Enter data type :char
Enter parameter name with '@':@p2
input or output parameter?:input
target_format.status : 256
Check for null
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Parameters
Param 2
Name Length [1]: 3
Name [3]: "@p2"
Status [1]: <unrecognized> (0x00)
User Type [4]: 0
Data Type [1]: LONGCHAR
Length [4]: 32676
Locale Length [1]: 0
Param 3
Name Length [1]: 3
Name [3]: "@p3"
Status [1]: PARAM_RETURN (0x01)
User Type [4]: 0
Data Type [1]: LONGCHAR
Length [4]: 32676
Locale Length [1]: 0
PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 4
Param data [4]: 1 (0x00000001)
Param 2
Length [4]: 12
Param data [12]: "TDS_LANGUAGE"
Param 3
Length [4]: 0
Param data [0]: [null]
- Procedure takes input
parameters to pass values to ASE
- Output parameters return
information from ASE
PARAMFMT2 Token (0x20); variable
length.
Length [4]: 53
Number of Params [2]: 3
Param 1
Name Length [1]: 3
Name [3]: "@p1"
Status [1]: <unrecognized>
(0x00)
User Type [4]: 0
Data Type [1]: INTN
Length [1]: 4
Locale Length [1]: 0
Group
Annual Conference, 2015
Parameters
Param 2
Name Length [1]: 3
Name [3]: "@p2"
Status [1]: <unrecognized> (0x00)
User Type [4]: 0
Data Type [1]: LONGCHAR
Length [4]: 32676
Locale Length [1]: 0
Param 3
Name Length [1]: 3
Name [3]: "@p3"
Status [1]: PARAM_RETURN (0x01)
User Type [4]: 0
Data Type [1]: LONGCHAR
Length [4]: 32676
Locale Length [1]: 0
PARAMS Token (0xD7); variable length.
Param 1
Length [1]: 4
Param data [4]: 1 (0x00000001)
Param 2
Length [4]: 12
Param data [12]: "TDS_LANGUAGE"
Param 3
Length [4]: 0
Param data [0]: [null]
- Procedure takes input
parameters to pass values to ASE
- Output parameters return
information from ASE
PARAMFMT2 Token (0x20); variable
length.
Length [4]: 53
Number of Params [2]: 3
Param 1
Name Length [1]: 3
Name [3]: "@p1"
Status [1]: <unrecognized>
(0x00)
User Type [4]: 0
Data Type [1]: INTN
Length [1]: 4
Locale Length [1]: 0
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response
ROWFMT Token (0xEE);
Length [2]: 25
Number of Columns [2]: 2
Column 1
Name Length [1]: 2
Name [2]: "c3"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 10
Data Type [1]: NUMN
Length [1]: 6
Precision [1]: 10
Scale [1]: 4
Locale Length [1]: 0
Column 2
Name Length [1]: 2
Name [2]: "c4"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 12
Data Type [1]: DATETIM
Length [0]: [8]
Locale Length [1]: 0
ROW Token (0xD1); variable length.
Column 1
Length [1]: 6
Row data [6]: 2.1000
Column 2
Length [0]: [8]
Row data [8]: 2015-03-08 21:56:51.533
- Row Results
-Format
-Data
- DONEINPROC tokens
-One each for Row results, 1 row
affected
-Return Status
-Counts as another row affected
-Output parameter
-Handled as row result mechanical
- Counts as another row affected
- RETURN Status
-TDS_RETURNSTATUS
-Returns status information to
client
-Can be used to flag various types
of errors
- Output parameter
-PARAMFMT
-PARAMS (Data returned, treated
like row result)
Group
Annual Conference, 2015
Response
ROWFMT Token (0xEE);
Length [2]: 25
Number of Columns [2]: 2
Column 1
Name Length [1]: 2
Name [2]: "c3"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 10
Data Type [1]: NUMN
Length [1]: 6
Precision [1]: 10
Scale [1]: 4
Locale Length [1]: 0
Column 2
Name Length [1]: 2
Name [2]: "c4"
Status [1]: ROW_UPDATABLE (0x10)
User Type [4]: 12
Data Type [1]: DATETIM
Length [0]: [8]
Locale Length [1]: 0
ROW Token (0xD1); variable length.
Column 1
Length [1]: 6
Row data [6]: 2.1000
Column 2
Length [0]: [8]
Row data [8]: 2015-03-08 21:56:51.533
- Row Results
-Format
-Data
- DONEINPROC tokens
-One each for Row results, 1 row
affected
-Return Status
-Counts as another row affected
-Output parameter
-Handled as row result mechanical
- Counts as another row affected
- RETURN Status
-TDS_RETURNSTATUS
-Returns status information to
client
-Can be used to flag various types
of errors
- Output parameter
-PARAMFMT
-PARAMS (Data returned, treated
like row result)
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Response
RETURNSTATUS Token (0x79); fixed length.
Length [0]: [4]
Status [4]: 0x00000000
PARAMFMT Token (0xEC); variable length.
Length [2]: 17
Number of Params [2]: 1
Param 1
Name Length [1]: 3
Name [3]: "@p3"
Status [1]: PARAM_RETURN
(0x01)
User Type [4]: 2
Data Type [1]: LONGCHAR
Length [4]: 16384
Locale Length [1]: 0
PARAMS Token (0xD7); variable length.
Param 1
Length [4]: 21
Param data [21]: "Sent from
sp_tds_proc"
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL
(0x0000)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count (unused) [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
Group
Annual Conference, 2015
Response
RETURNSTATUS Token (0x79); fixed length.
Length [0]: [4]
Status [4]: 0x00000000
PARAMFMT Token (0xEC); variable length.
Length [2]: 17
Number of Params [2]: 1
Param 1
Name Length [1]: 3
Name [3]: "@p3"
Status [1]: PARAM_RETURN
(0x01)
User Type [4]: 2
Data Type [1]: LONGCHAR
Length [4]: 16384
Locale Length [1]: 0
PARAMS Token (0xD7); variable length.
Param 1
Length [4]: 21
Param data [21]: "Sent from
sp_tds_proc"
DONE Token (0xFD); fixed length.
Length [0]: [8]
Status [2]: DONE_FINAL
(0x0000)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count (unused) [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
DONEINPROC Token (0xFF); fixed length.
Length [0]: [8]
Status [2]: DONE_MORE + DONE_COUNT
+ DONE_EVENT (0x0051)
TranState [2]: TDS_TRAN_PROGRESS
(0x0002)
Count [4]: 1
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
RETURNSTATUS
Used to provide status from the procedure.
Application code can test this value to determine various events
ASE provides some negative values for various errors
Coder can test values – so like”8” might mean we just called 8 triggers to update
various tables.
Group
Annual Conference, 2015
RETURNSTATUS
Used to provide status from the procedure.
Application code can test this value to determine various events
ASE provides some negative values for various errors
Coder can test values – so like”8” might mean we just called 8 triggers to update
various tables.
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Summary
TDS used to troubleshoot errors
On client or ASE?
Reverse Engineer
Can use the output to write test code to represent
problem
Understand communication between Server and Client
Group
Annual Conference, 2015
Summary
TDS used to troubleshoot errors
On client or ASE?
Reverse Engineer
Can use the output to write test code to represent
problem
Understand communication between Server and Client
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
Question and Answer
Questions
Group
Annual Conference, 2015
Question and Answer
Questions
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
ISUG articles
•TDS Revealed: A Primer to Understanding the
Communication Language of ASE
•Issue 2, 2009
•Chomping on the Network with Wireshark
•November 2010 and January 2011
•TDS Case Studies: Root Cause Analysis Techniques
for ASE Drivers
•September 2010
Group
Annual Conference, 2015
ISUG articles
•TDS Revealed: A Primer to Understanding the
Communication Language of ASE
•Issue 2, 2009
•Chomping on the Network with Wireshark
•November 2010 and January 2011
•TDS Case Studies: Root Cause Analysis Techniques
for ASE Drivers
•September 2010
(c) 2015 Independent SAP Technical User
Group
Annual Conference, 2015
More Resources
·Product Documentation
·
http://help.sap.com/adaptive-server-enterprise?current= database&show_children= false
·How to get best results from an SAP Search
·https://service.sap.com/sap/support/notes/2081285
·Service Market Place/Support
·https://service.sap.com
·https://support.sap.com/home.html
·SAP Communities
·http://scn.sap.com/community/sybase-adaptive-server-enterprise
·http://scn.sap.com/community/developer-center/oltp-db
·Social Media Product Support Channels
·https://www.facebook.com/SapProductSupport
·https://twitter.com/SAPSupporthelp
Group
Annual Conference, 2015
More Resources
·Product Documentation
·
http://help.sap.com/adaptive-server-enterprise?current= database&show_children= false
·How to get best results from an SAP Search
·https://service.sap.com/sap/support/notes/2081285
·Service Market Place/Support
·https://service.sap.com
·https://support.sap.com/home.html
·SAP Communities
·http://scn.sap.com/community/sybase-adaptive-server-enterprise
·http://scn.sap.com/community/developer-center/oltp-db
·Social Media Product Support Channels
·https://www.facebook.com/SapProductSupport
·https://twitter.com/SAPSupporthelp
Categories
BusinessDownload
Download Slideshow Get the original presentation fileQuick Actions
Statistics
- Views 2,592
- Slides 97
- Favorites 3
- Age 3825 days
Related Slideshows
1
DTI BPI Pivot Small Business - BUSINESS START UP PLAN
MeljunCortes
28 views
1
CATHOLIC EDUCATIONAL Corporate Responsibilities
MeljunCortes
30 views
11
Karin Schaupp – Evocation; lançamento: 2000
alfeuRIO
28 views
10
Pillars of Biblical Oneness in the Book of Acts
JanParon
26 views
31
7-10. STP + Branding and Product & Services Strategies.pptx
itsyash298
27 views
44
Business Legislation PPT - UNIT 1 jimllpkggg
slogeshk98
29 views