Calling Stored Procedure from VB.net timeout error

Question

When calling a stored procedure from vb.net is there a default SQL timeout time if no timeout is specified in the connection string? I am unsure if there is a CommandTimeout specified in the connection string but am going through all the possibilites.

Example if no results after 30 seconds (or more) throw:

`System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.`

SQL Profiler says that the script runs and ends in 30 seconds when the program timesout..

Tthe script runs without error in about 1 minute 45 seconds by itself in SQL server.

Answer 1

best practice is to try to improve the SQL to run in less than 30 seconds. That is much better than increasing the VB timeout length.

To get the best help with the SQL performance issue, post the following:

• the actual sql
• the table structure (listing the indexes)
• the execution plan.

To display the execution plan. IN SQL Server management Studio run this command:

SET SHOWPLAN_XML ON

then run your slow sql. the actual query will not be run, but the execution plan will be displayed in xml format.

Answer 2

SqlCommand cmd;
.....
cmd.CommandTimeout = 120;

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Answer 3

The timeout value for executing SQL isn't stored in the connection string, it is stored in the SqlCommand as SqlCommand.CommandTimeout.

Default is 30 seconds.

Answer 4

Ther are two types of timeout exception that could be raised by SqlClient objects: SqlConnection and SqlCommand.

SqlConnection timeout exception is raised when app is trying to establish connection but not successful within the given time period. This is 500 sec I believe.

SqlCommand Timeout decides how long time a command is given for the application that uses the SQLCommand to wait for SQL Server to finish the operation. This one is 30 secs.

When setting up the SQLCommand, change the CommandTimeout to be higher

eg.

cmd.CommandTimeout = 300