#60 Adaptiver SErver conneciton timeout without SET NOCOUNT ON
PROBLEM: I was experiencing similar results to the link below (my debug trace also included). A valid stored procedure call appears to timeout and initiates a TDS_INT_CANCEL after receiving error 20003 "Adaptive Server connection timed out". Adjusting connection timeouts had no effect.
I was able to esaily reproduce with a test harness (unfortunately not portable or I would post) in php using both PDO and MSSQL connections on
CentOS release 5.5 (Final) with
freetds.i386 0.91-1.el5
SOLUTION: include the statement "SET NOCOUNT ON" in stored procedure. FreeTDS driver completes successfully and returns a proper result set.
Where I found a solution:
http://dba.stackexchange.com/questions/28692/sql-08s01-error-in-freetds-azure-when-executing-stored-proc-for-a-few-minutes-fr
My freetds log trace calling the stored procedure
12:50:04.761214 6298 (util.c:156):Changed query state from QUERYING to PENDING
12:50:04.761220 6298 (net.c:741):Sending packet
0000 01 01 00 96 00 00 01 00-45 00 58 00 45 00 43 00 |........ E.X.E.C.|
0010 20 00 75 00 74 00 6d 00-5f 00 73 00 61 00 6d 00 | .u.t.m. _.s.a.m.|
0020 6c 00 5f 00 67 00 65 00-74 00 55 00 73 00 65 00 |l._.g.e. t.U.s.e.|
0030 72 00 49 00 44 00 32 00-20 00 27 00 6f 00 76 00 |r.I.D.2. .'.o.v.|
0040 11 00 12 00 11 00 11 00-11 00 40 00 69 00 6c 00 |b.c.d.e. a.@.a.l.|
12:50:04.761502 6298 (dblib.c:4639):dbsqlok(0x9d2ca58)
12:50:04.761519 6298 (dblib.c:4669):dbsqlok() not done, calling tds_process_tokens()
12:50:04.761526 6298 (token.c:540):tds_process_tokens(0x9d2d238, 0xbf9d4b48, 0xbf9d4b44, 0x6914)
12:50:04.761533 6298 (util.c:156):Changed query state from PENDING to READING
12:50:35.208977 6298 (util.c:331):tdserror(0x9b83e18, 0x9d2d238, 20003, 0)
12:50:35.209061 6298 (dblib.c:7929):dbperror(0x9d2ca58, 20003, 0)
12:50:35.209074 6298 (dblib.c:7981):20003: "Adaptive Server connection timed out"
12:50:35.209082 6298 (dblib.c:5780):dbgetuserdata(0x9d2ca58)
12:50:35.209095 6298 (dblib.c:8002):"Adaptive Server connection timed out", client returns 2 (INT_CANCEL)
12:50:35.209105 6298 (util.c:361):tdserror: client library returned TDS_INT_CANCEL(2)
12:50:35.209111 6298 (util.c:384):tdserror: returning TDS_INT_CANCEL(2)
12:50:35.209165 6298 (util.c:156):Changed query state from READING to DEAD
12:50:35.209178 6298 (token.c:555):processing result tokens. marker is 0()
12:50:35.209188 6298 (token.c:122):tds_process_default_tokens() marker is 0()
12:50:35.209195 6298 (token.c:125):leaving tds_process_default_tokens() connection dead
12:50:35.209201 6298 (util.c:104):logic error: cannot change query state from DEAD to PENDING
12:50:35.221506 6298 (dblib.c:3196):dbcancel(0x9d2ca58)
12:50:35.221530 6298 (dblib.c:7929):dbperror(0x9d2ca58, 20047, 0)
12:50:35.221542 6298 (dblib.c:7981):20047: "DBPROCESS is dead or not enabled"
12:50:35.221549 6298 (dblib.c:5780):dbgetuserdata(0x9d2ca58)
12:50:35.221556 6298 (dblib.c:8002):"DBPROCESS is dead or not enabled", client returns 2 (INT_CANCEL)
