Defines the maximum number of seconds during which a query should execute for the current SQL Server connection.

Syntax

SET PRO_CONNECT QUERY_TIMEOUT n ;

Parameter

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

• Both database and non-database administrators can execute this command. The value specified by n applies only to current SQL connection and is in effect for the duration of the current connection.
• Timeout value can be cleared by specifying a value of 0 on subsequent execution of the statement SET PRO_CONNECT QUERY_TIMEOUT 0.
• To enable timeout at server level use the PRO_SERVER statement, SET PRO_SERVER 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.

Example

This example sets the query timeout to 30 seconds:

Statement stmt = connection.createStatement();
String MySetQueryTimeout;
String MyClearQueryTimeout;
MySetTimeout = "SET PRO_CONNECT QUERY_TIMEOUT 30";
MyClearTimeout = "SET PRO_CONNECT 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 );