setQueryTimeout

[Howard]

Hi,

I'm encountering weird behaviors with the following code [using the
Microsoft JDBC drivers to connect to a SQL server v8.00.760 (w/ SP3)
running on Windows 2000 Professional (w/ SP3)]. The JVM I'm using
is Sun's 1.4 JVM.

Connection conn;
CallableStatement cstmt =
(CallableStatement)conn.prepareCall(query);
cstmt.setQueryTimeout(1);
ResultSet rs = cstmt.executeQuery();

PART I
------

If query is a call to the following stored procedure (which takes my
SQL server about 7-8 seconds to execute):

CREATE PROCEDURE zwait as
declare @i int
set @i = 1000000
while @i > 0
begin
set @i = @i - 1
end

I get 1 of 2 exceptions, depending on whether not I'm executing
the query for the first time or not.

On the first exection, I get EXCEPTION 1:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Execution timeout expired.
at com.microsoft.jdbc.base.BaseExceptions.createException(...)
at com.microsoft.jdbc.base.BaseExceptions.getException(...)
at com.microsoft.jdbc.base.BaseStatement.preprocessExceptionFromCommonExecute(...)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(...)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(...)

On successive executions, I get EXCEPTION 2:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]The operation was cancelled at the user's request.
at com.microsoft.jdbc.base.BaseExceptions.createException(...)
at com.microsoft.jdbc.base.BaseExceptions.getException(...)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(...)
at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(...)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(...)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processOutstandingCancelAcknowledgements(...)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(...)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(...)
at com.microsoft.jdbc.base.BaseStatement.getNextResultType(...)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(...)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(...)

My first question is why do I get two different exception messages?

PART II
-------

If my query selects from the results of many inner joins and takes
more than the timeout period to run (i.e., 1 second in my example),
I do *not* get any "timeout" exceptions immediately. Instead I get
the following behavior:

1. Run query first time
2. Query times out w/o any exception, and returns me
an empty result set
3. Run query 2nd time
[by creating a *new* statement on the *same* connection]
4. I immediately get the Exception 2 from Part I above.

This is seems like a bug to me because I would expect the exception
to be thrown after after step (1) instead of after step (3). Has
anyone else seen this before? If so, is there a known fix for it?
Thanks for any help.