FreeTDS Development Group

["Glenn, Chris" <Glenn AT addsys.com>]

I sent this several weeks ago with no response.  Am I doing something
wrong?

-----Original Message-----
From: Glenn, Chris 
Sent: Tuesday, December 19, 2006 2:23 PM
To: 'freetds AT lists.ibiblio.org'
Subject: Issue with return status from stored procedure with a warning
message.

I have a CT-Lib application that calls a stored procedure.  One of the
arguments to the stored procedure indicates whether or not to begin a
transaction.  The stored procedure, if required, will begin the
transaction but not do a commit.  This will be done later in the
application flow.  The stored procedure returns in the status code the
key piece of information that the application requires.

When the 'begin tran' is executed, an error code 266 is generated
(Transaction count after EXECUTE indicates that a COMMIT of ROLLBACK
TRANSACTION statement is missing...) with a severity level of 16.
According to Microsoft's documentation (link below), this error code is
just a warning message and can be ignored.
http://msdn2.microsoft.com/en-us/library/aa258721(SQL.80).aspx

What appears to be happening is that the return status is never
processed and passed back to my CT-Lib application (I assume) because of
the error code 266.

I've attached the excerpt from the debug log when calling the stored
procedure with the 'begin tran' mode and without.

Chris Glenn
 <<SP with begin trans.txt>>  <<SP without begin trans.txt>> 
ct.c:215:setting command state from READY to IDLE
ct.c:215:setting command state from IDLE to IDLE
ct.c:729:ct_command() added rpcname "trxseqnum_$sp"
ct.c:215:setting command state from IDLE to READY
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe63e0 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @table_key
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe63e4 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @lock_val
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe63e8 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @min_max
ct.c:809:ct_send() command_type = 149
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @table_key, varint size 1 column_type 56 
size 4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x8853820, row_size = 0
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @lock_val, varint size 1 column_type 56 size 
4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x884cd10, row_size = 0
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @min_max, varint size 1 column_type 56 size 
4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x884cd20, row_size = 0
util.c:120:Changing query state from IDLE to QUERYING
mem.c:565:tds_free_all_results()
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
util.c:120:Changing query state from QUERYING to PENDING
net.c:724:Sending packet
0000 03 01 00 77 00 00 01 00-0d 00 74 00 72 00 78 00 |...w.... ..t.r.x.|
0010 73 00 65 00 71 00 6e 00-75 00 6d 00 5f 00 24 00 |s.e.q.n. u.m._.$.|
0020 73 00 70 00 00 00 0a 40-00 74 00 61 00 62 00 6c |s.p....@ .t.a.b.l|
0030 00 65 00 5f 00 6b 00 65-00 79 00 00 26 04 04 4e |.e._.k.e .y..&..N|
0040 00 00 00 09 40 00 6c 00-6f 00 63 00 6b 00 5f 00 |....@.l. o.c.k._.|
0050 76 00 61 00 6c 00 00 26-04 04 01 00 00 00 08 40 |v.a.l..& .......@|
0060 00 6d 00 69 00 6e 00 5f-00 6d 00 61 00 78 00 00 |.m.i.n._ .m.a.x..|
0070 26 04 04 00 00 00 00   -                        |&......|

ct.c:215:setting command state from READY to SENT
ct.c:1065:ct_results()
token.c:519:tds_process_tokens(0x88522d8, 0xbffe5ea4, 0xbffe5ea8, 0x6914)
util.c:120:Changing query state from PENDING to READING
net.c:499:Received header
0000 04 01 01 63 00 3b 01 00-                        |...c.;..|

net.c:579:Received packet
0000 79 fd ff ff ff aa 4a 01-0a 01 00 00 02 10 8c 00 |y.....J. ........|
0010 54 00 72 00 61 00 6e 00-73 00 61 00 63 00 74 00 |T.r.a.n. s.a.c.t.|
0020 69 00 6f 00 6e 00 20 00-63 00 6f 00 75 00 6e 00 |i.o.n. . c.o.u.n.|
0030 74 00 20 00 61 00 66 00-74 00 65 00 72 00 20 00 |t. .a.f. t.e.r. .|
0040 45 00 58 00 45 00 43 00-55 00 54 00 45 00 20 00 |E.X.E.C. U.T.E. .|
0050 69 00 6e 00 64 00 69 00-63 00 61 00 74 00 65 00 |i.n.d.i. c.a.t.e.|
0060 73 00 20 00 74 00 68 00-61 00 74 00 20 00 61 00 |s. .t.h. a.t. .a.|
0070 20 00 43 00 4f 00 4d 00-4d 00 49 00 54 00 20 00 | .C.O.M. M.I.T. .|
0080 6f 00 72 00 20 00 52 00-4f 00 4c 00 4c 00 42 00 |o.r. .R. O.L.L.B.|
0090 41 00 43 00 4b 00 20 00-54 00 52 00 41 00 4e 00 |A.C.K. . T.R.A.N.|
00a0 53 00 41 00 43 00 54 00-49 00 4f 00 4e 00 20 00 |S.A.C.T. I.O.N. .|
00b0 73 00 74 00 61 00 74 00-65 00 6d 00 65 00 6e 00 |s.t.a.t. e.m.e.n.|
00c0 74 00 20 00 69 00 73 00-20 00 6d 00 69 00 73 00 |t. .i.s.  .m.i.s.|
00d0 73 00 69 00 6e 00 67 00-2e 00 20 00 50 00 72 00 |s.i.n.g. .. .P.r.|
00e0 65 00 76 00 69 00 6f 00-75 00 73 00 20 00 63 00 |e.v.i.o. u.s. .c.|
00f0 6f 00 75 00 6e 00 74 00-20 00 3d 00 20 00 30 00 |o.u.n.t.  .=. .0.|
0100 2c 00 20 00 63 00 75 00-72 00 72 00 65 00 6e 00 |,. .c.u. r.r.e.n.|
0110 74 00 20 00 63 00 6f 00-75 00 6e 00 74 00 20 00 |t. .c.o. u.n.t. .|
0120 3d 00 20 00 31 00 2e 00-06 56 00 4d 00 57 00 41 |=. .1... .V.M.W.A|
0130 00 52 00 45 00 0d 74 00-72 00 78 00 73 00 65 00 |.R.E..t. r.x.s.e.|
0140 71 00 6e 00 75 00 6d 00-5f 00 24 00 73 00 70 00 |q.n.u.m. _.$.s.p.|
0150 00 00 fe 02 00 e0 00 00-00 00 00                |........ ...|

token.c:534:processing result tokens.  marker is  79(RETURNSTATUS)
token.c:534:processing result tokens.  marker is  aa(ERROR)
token.c:107:tds_process_default_tokens() marker is aa(ERROR)
token.c:2404:tds_process_msg() reading message from server
read.c:179:tds_get_string: reading 280 from wire to give 140 to client.
read.c:179:tds_get_string: reading 12 from wire to give 6 to client.
read.c:179:tds_get_string: reading 26 from wire to give 13 to client.
token.c:2469:tds_process_msg() calling client msg handler
token.c:2482:tds_process_msg() returning TDS_SUCCEED
token.c:534:processing result tokens.  marker is  fe(DONEPROC)
token.c:2108:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2124:tds_process_end() state set to TDS_IDLEct.c:215:setting command state from READY to IDLE
ct.c:215:setting command state from IDLE to IDLE
ct.c:729:ct_command() added rpcname "trxseqnum_$sp"
ct.c:215:setting command state from IDLE to READY
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe2c40 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @table_key
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe2c44 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @lock_val
ct.c:3326:ct_param()
ct.c:3327:ct_param() data addr = 0xbffe2c48 data length = 4
ct.c:4294: _ct_fill_param() status = 256
ct.c:1970:_ct_get_server_type(8)
ct.c:3358: ct_param() added rpc parameter @min_max
ct.c:809:ct_send() command_type = 149
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @table_key, varint size 1 column_type 56 
size 4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x9d6a9b0, row_size = 0
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @lock_val, varint size 1 column_type 56 size 
4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x9cdcc60, row_size = 0
ct.c:4207:paraminfoalloc: status = 256, maxlen 4
ct.c:4208:paraminfoalloc: name = @min_max, varint size 1 column_type 56 size 
4, 4 column_cur_size 4 column_output = 0
ct.c:4047:paramrowalloc, size = 4, data = 0x9cdcc70, row_size = 0
util.c:120:Changing query state from IDLE to QUERYING
mem.c:565:tds_free_all_results()
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
query.c:1248:tds_put_data_info putting param_name
query.c:1277:tds_put_data_info putting status
query.c:1371:tds_put_data: colsize = 4
query.c:1400:tds_put_data: not null param varint_size = 1
util.c:120:Changing query state from QUERYING to PENDING
net.c:724:Sending packet
0000 03 01 00 77 00 00 01 00-0d 00 74 00 72 00 78 00 |...w.... ..t.r.x.|
0010 73 00 65 00 71 00 6e 00-75 00 6d 00 5f 00 24 00 |s.e.q.n. u.m._.$.|
0020 73 00 70 00 00 00 0a 40-00 74 00 61 00 62 00 6c |s.p....@ .t.a.b.l|
0030 00 65 00 5f 00 6b 00 65-00 79 00 00 26 04 04 4e |.e._.k.e .y..&..N|
0040 00 00 00 09 40 00 6c 00-6f 00 63 00 6b 00 5f 00 |....@.l. o.c.k._.|
0050 76 00 61 00 6c 00 00 26-04 04 00 00 00 00 08 40 |v.a.l..& .......@|
0060 00 6d 00 69 00 6e 00 5f-00 6d 00 61 00 78 00 00 |.m.i.n._ .m.a.x..|
0070 26 04 04 00 00 00 00   -                        |&......|

ct.c:215:setting command state from READY to SENT
ct.c:1065:ct_results()
token.c:519:tds_process_tokens(0x9cdb2d8, 0xbffe2704, 0xbffe2708, 0x6914)
util.c:120:Changing query state from PENDING to READING
net.c:499:Received header
0000 04 01 00 16 00 33 01 00-                        |.....3..|

net.c:579:Received packet
0000 79 fd ff ff ff fe 00 00-e0 00 00 00 00 00       |y....... ......|

token.c:534:processing result tokens.  marker is  79(RETURNSTATUS)
token.c:704:tds_process_tokens: return status is -3                           
          <===== This is what I'm looking for!
util.c:120:Changing query state from READING to PENDING
ct.c:1122:ct_results() process_result_tokens returned 1 (type 4043)
mem.c:565:tds_free_all_results()
ct.c:4026:generating return status row. type = 56(int), varint_size 0
ct.c:1399:ct_bind() datafmt count = 1 column_number = 1
ct.c:1455:ct_fetch()
ct.c:1669:_ct_bind_data()
ct.c:1676:_ct_bind_data(): column_type: 56 column_len: 4
ct.c:1970:_ct_get_server_type(8)
ct.c:1857:_ct_get_client_type(type 56, user 0, size 4)
cs.c:353:cs_convert()
ct.c:1970:_ct_get_server_type(8)
ct.c:1970:_ct_get_server_type(8)
cs.c:360:cs_convert() srctype = 56 (4) desttype = 56 (4)
cs.c:385:cs_convert() srctype = desttype
ct.c:1455:ct_fetch()
ct.c:1065:ct_results()
token.c:519:tds_process_tokens(0x9cdb2d8, 0xbffe2704, 0xbffe2708, 0x6914)
util.c:120:Changing query state from PENDING to READING
token.c:534:processing result tokens.  marker is  fe(DONEPROC)
token.c:2108:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2124:tds_process_end() state set to TDS_IDLE
util.c:120:Changing query state from READING to IDLE
util.c:120:Changing query state from IDLE to PENDING
ct.c:1122:ct_results() process_result_tokens returned 1 (type 4053)
ct.c:2322:ct_res_info()
ct.c:2346:ct_res_info(): Number of rows is -1
ct.c:1065:ct_results()
ct.c:2322:ct_res_info()
ct.c:2346:ct_res_info(): Number of rows is -1
ct.c:1065:ct_results()
token.c:519:tds_process_tokens(0x9cdb2d8, 0xbffe2704, 0xbffe2708, 0x6914)
token.c:522:tds_process_tokens() state is COMPLETED