Microsoft KB Archive/894561

= FIX: You do not receive a &quot;Timeout expired&quot; error message after subsequent tries to execute a stored procedure even though you use the setQueryTimeout method to set a time-out through the SQL Server 2000 Driver for JDBC =

Article ID: 894561

Article Last Modified on 3/23/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC

-





SYMPTOMS
When you use the Microsoft SQL Server 2000 Driver for JDBC, you may notice that you do not receive a &quot;Timeout expired&quot; error message after subsequent tries to execute a stored procedure. This behavior occurs even you set a time-out by using the setQueryTimeout method and that time-out has elapsed. The function call to execute a statement returns. However, you do not receive an exception and the record set is empty.



RESOLUTION
To resolve this problem, obtain SQL Server 2000 Driver for JDBC Service Pack 3 (SP3). For more information, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyID=07287b11-0502-461a-b138-2aa54bfdc03a&DisplayLang=en



WORKAROUND
To work around this problem, use one of the following methods:  Run the DBCC FREEPROCCACHE statement before you call the second stored procedure.  Specify the &quot;WITH RECOMPILE&quot; option when you create the stored procedure. For example, see the following code: CREATE PROCEDURE [dbo].[usp_myProc] WITH RECOMPILE AS   SELECT * FROM tableWithManyRows GO 



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the behavior
  Create the following stored procedure: USE pubs GO   CREATE PROCEDURE [dbo].[usp_myProc] AS   SELECT * FROM aTableWithManyRows GO   Compile and then run the following Java code: import java.sql.*; public class Test {       public static void main(String[] args) throws Exception {           Class.forName(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;);

// Set up a connection. String url = &quot;jdbc:microsoft:sqlserver://<Server>:1433;databasename=jdbc;SelectMethod=cursor;&quot;; Connection conn = DriverManager.getConnection(url, &quot;<UserID>&quot;, &quot;<Password>&quot;); System.out.println(&quot;\nGot a connection.&quot;);

// Execute the stored procedure. String strSQL = &quot;{call usp_myProc}&quot;;

CallableStatement cstmt = null; ResultSet rs = null;

try {               // QUERY 1 System.out.println(&quot;\nRunning the first query.&quot;); long lStart = System.currentTimeMillis; cstmt = conn.prepareCall(strSQL); cstmt.setQueryTimeout(60); // Set the time-out to a high value. rs = cstmt.executeQuery; long lEnd = System.currentTimeMillis; System.out.println(&quot;Time taken = &quot; + (lEnd - lStart) + &quot; ms\n&quot;);

// QUERY 2 System.out.println(&quot;\nRunning the second query.&quot;); cstmt = (CallableStatement) conn.prepareCall(strSQL); cstmt.setQueryTimeout(1);  // Set the time-out to one (1) second. lStart = System.currentTimeMillis; rs = cstmt.executeQuery; lEnd = System.currentTimeMillis; System.out.println(&quot;Time taken = &quot; + (lEnd - lStart) + &quot; ms\n&quot;);

// The time-out should occur before the query has finished running. throw new Exception(&quot;The second query should not have returned to this line.\n&quot;); }            catch (SQLException e)            { // A time-out exception should occur. if (e.getMessage.indexOf(&quot;Execution timeout expired&quot;) != -1) {                   System.out.println(&quot;Query timed out as expected.&quot;); e.printStackTrace; }                else {                   throw e;                } }           finally {               if (rs != null) {                   rs.close; rs = null; }               if (cstmt != null) {                   cstmt.close; cstmt = null; }           }

conn.close; System.out.println(&quot;Reached the end.&quot;); }   } Note In this code, replace ,  , and   with the name of your computer that is running SQL Server, your user ID, and your password. </li></ol>

<div class="references_section">