How to increase time in web.config for executing sql query

Question

When I am running a query in web application, I'm getting a null value. Same query directly in SQL Management Studio returns results.

I think that the problem is a timeout. How can I increase the time for execution of query in web application? In my web.config : connectionstring, there is no code for timeout. If I choose a timeout there, will that affect other parts of my system?

Solution 1

SQL Server has no setting to control query timeout in the connection string, and as far as I know this is the same for other major databases. But, this doesn't look like the problem you're seeing: I'd expect to see an exception raised

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

if there genuinely was a timeout executing the query.

If this does turn out to be a problem, you can change the default timeout for a SQL Server database as a property of the database itself; use SQL Server Manager for this.

Be sure that the query is exactly the same from your Web application as the one you're running directly. Use a profiler to verify this.

Solution 2

I realise I'm a litle late to the game, but just spent over a day on trying to change the timeout of a webservice. It seemed to have a default timeout of 30 seconds. I after changing evry other timeout value I could find, including:

• DB connection string Connect Timeout
• httpRuntime executionTimeout
• basicHttpBinding binding closeTimeout
• basicHttpBinding binding sendTimeout
• basicHttpBinding binding receiveTimeout
• basicHttpBinding binding openTimeout

Finaley I found that it was the SqlCommand timeout that was defaulting to 30 seconds.

I decided to just duplicate the timeout of the connection string to the command. The connection string is configured in the web.config.

Some code:

namespace ROS.WebService.Common
{
  using System;
  using System.Configuration;
  using System.Data;
  using System.Data.SqlClient;

  public static class DataAccess
  {
    public static string ConnectionString { get; private set; }

    static DataAccess()
    {
      ConnectionString = ConfigurationManager.ConnectionStrings["ROSdb"].ConnectionString;
    }

    public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] sqlParams)
    {
      using (SqlConnection conn = new SqlConnection(DataAccess.ConnectionString))
      {
        using (SqlCommand cmd = new SqlCommand(cmdText, conn) { CommandType = cmdType, CommandTimeout = conn.ConnectionTimeout })
        {
          foreach (var p in sqlParams) cmd.Parameters.Add(p);
          cmd.Connection.Open();
          return cmd.ExecuteNonQuery();
        }
      }
    }
  }
}

Change introduced to "duplicate" the timeout value from the connection string:CommandTimeout = conn.ConnectionTimeout

Solution 3

You should add the httpRuntime block and deal with executionTimeout (in seconds).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
...
 <system.web>
   <httpRuntime executionTimeout="90" maxRequestLength="4096"
    useFullyQualifiedRedirectUrl="false"
    minFreeThreads="8"
    minLocalRequestFreeThreads="4"
    appRequestQueueLimit="100" />
 </system.web>
... 
</configuration>

For more information, please, see msdn page.

Solution 4

I think you can't increase the time for query execution, but you need to increase the timeout for the request.

Execution Timeout Specifies the maximum number of seconds that a request is allowed to execute before being automatically shut down by ASP.NET. (Default time is 110 seconds.)

For Details, please have a look at https://msdn.microsoft.com/en-us/library/e1f13641%28v=vs.100%29.aspx

You can do in the web.config. e.g

<httpRuntime maxRequestLength="2097152" executionTimeout="600" />

View more solutions

Comments

[Aisah Hamzah]

This is the timeout for the execution of the ASP.NET page itself, not for the SQL query.

[Triynko]

The httpRuntime executionTimeout should be probably be at least as long as the query execution timeout if you're running the query as part of a request, otherwise the request will timeout and be cancelled before the query has a chance to complete.

[symbiont]

are the settings in SQL Management Studio still for itself instead of for SQL Server? (i suspect so)