Defines the maximum number of seconds during which a query should execute for all the client connections spawned by the SQL Server.

Syntax

SET PRO_SERVER QUERY_TIMEOUT n ;

Parameters

n

Indicates the maximum number of seconds during which a query should execute before it is automatically cancelled by the SQL server. Setting the value of n to 0 disables a previously set query timeout.

Notes

• Execution of this command is restricted to database administrators. Any value set with this command is in effect for the duration that the database is up and running.
• The value specified by n applies to all connections of the SQL server until the timeout value is cleared.
• Timeout value can be cleared by specifying a value of 0 on subsequent execution of the statement SET PRO_SERVER QUERY_TIMEOUT 0.
• To enable timout for an individual connection, use PRO_CONNECT statement SET PRO_CONNECT QUERY_TIMEOUT N.
• When both the server and connection timeouts are set, the lower of the timeout values takes the precedence.
• To set timeout when working with JDBC and ODBC clients, execute the SET PRO_SERVER/CONNECT statement before executing the SQL statements.
• Timeout is applicable to only select statements. Other DDL and DML statements are not cancelled by SQL server when they exceed the specified timeout value.
• Timeout accounts only the query execution time i.e., the time it takes to prepare, execute and fetch the results. It does not account the time the clients take to stream the data.

This example sets the query timeout to 30 seconds:

Statement stmt = connection.createStatement();
String MySetQueryTimeout;
String MyClearQueryTimeout;
MySetTimeout = "SET PRO_SERVER QUERY_TIMEOUT 30";
MyClearTimeout = "SET PRO_SERVER QUERY_TIMEOUT 0";
// Set SQL Server timeout for query execute and fetch
stmt.executeUpdate( MySetQueryTimeout );
// Add code here to perform queries
// Clear SQL Server timeout for query execute and fetch
stmt.executeUpdate( MyClearQueryTimeout );