Calling a stored procedure from a VB.net timeout error

Question

When calling a stored procedure from vb.net, is there a default SQL timeout if no timeout is specified in the connection string? I am not sure if there is a CommandTimeout in the connection CommandTimeout , but I am looking through all the possibilities.

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

 `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 starts and ends after 30 seconds when the program time ends.

The T script runs without errors after about 1 minute 45 seconds on the SQL server.

Answer 1

The timeout value for executing SQL is not stored in the connection string; it is stored in SqlCommand as SqlCommand.CommandTimeout .

The default value is 30 seconds.

Answer 2

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

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

Answer 3

Ther are two types of timeout exceptions that SqlClient objects can raise: SqlConnection and SqlCommand.

A SqlConnection timeout exception occurs when an application tries to establish a connection but is not successful within a given period of time. I think it is 500 seconds.

SqlCommand The timeout decides how long it takes for the application command to use SQLCommand to wait for the SQL Server operation to complete. This is 30 seconds.

When configuring SQLCommand, change CommandTimeout to a higher

eg.

 cmd.CommandTimeout = 300 

Answer 4

Best practice is to try to improve SQL performance in less than 30 seconds. This is much better than increasing the length of the VB timeout.

To get the best help when releasing SQL performance, post the following:

• actual sql
• table structure (index listing)
• implementation plan.

To display the execution plan. In SQL Server Management Studio, run the following command:

 SET SHOWPLAN_XML ON 

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

Answer 5

When using the Entity Framework, CommandTimeout also exists in ModelContainer:

 Using dbcontext As New MyDatabase.ModelsContainer() dbcontext.CommandTimeout = 120 'Linq command' End Using