Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Question

I am running a stored procedure but while running it shows error like

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I have set my web config like

   <add name="SAMPLECONNECTION" connectionString="Data Source=ADMIN\SQLEXPRESS;Initial Catalog=PFGRT;Persist Security Info=True;MultipleActiveResultSets=true;User ID=sa;connect Timeout =1000000;Password=PASSWORD"
  providerName="System.Data.SqlClient" />

I tried like set the connect Timeout = 0; also, but I get the same error.

How can I solve it?

My Stored Procedure calling class

public DataSet adFeedBackReportFacultyWise(string SetID, string FacultyID, string SubjectID,string groupid)
{
    DbUtil du = new DbUtil();
    Dictionary<string, SqlParameter> cmdParameters = new Dictionary<string, SqlParameter>();
    cmdParameters["SetID"] = new SqlParameter("SetID", SetID);
    cmdParameters["FacultyID"] = new SqlParameter("FacultyID", FacultyID);
    cmdParameters["SubjectID"] = new SqlParameter("SubjectID", SubjectID);
    cmdParameters["groupid"] = new SqlParameter("groupid", groupid);

    return du.ExecuteQuery("adFeedBackReportFacultyWise", cmdParameters);
}

My DButil Class is

public class DbUtil
{
    string connection = ConfigurationManager.ConnectionStrings["gatikkcmsConnectionString"].ConnectionString;

    public SqlDataAdapter ad;
    public DataTable dt = new DataTable();
    SqlConnection conn;
    SqlCommand cmd = new SqlCommand();
    public DbUtil()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["gatikkcmsConnectionString"].ConnectionString);
        cmd = null;
    }

    public SqlConnection getConnection()
    {
        return new SqlConnection(connection);
    }
   public int ExecuteCommand(string storedProcName, Dictionary<string, SqlParameter> procParameter)
    {
        int rc;
        using (SqlConnection cn = getConnection())
        {
            cn.Open();
            // create a SQL command to execute the stored procedure
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = storedProcName;
                // assign parameters passed in to the command
                foreach (var procParameters in procParameter)
                {
                    cmd.Parameters.Add(procParameters.Value);
                }
                rc = cmd.ExecuteNonQuery();
            }
            cn.Close();
        }
        return rc;
    }
}

A `connection timeout` is different from a `command timeout`. The former can be set in the connection string, but the latter will need to be applied programmatically to the command that's being executed. That said, the default is, I think, 30 seconds - how long does this proc take to run? — Brendan Green, Sep 24 '15 at 05:07
Increase the `Connection Timeout`. By default it is 30 seconds. You can assign your own value to it. — Krsna Kishore, Sep 24 '15 at 05:08
@SemilSebastian What's the definition of `DbUtil`? If it comes from a framework, which one? — Brendan Green, Sep 24 '15 at 05:10
I guess then that internally you are creating some kind of `DbCommand` instance that ultimately executes the proc. You should be able to set the command timeout there. — Brendan Green, Sep 24 '15 at 05:13
try to add this in your connection class `sql.CommandTimeout = 120;` — Krsna Kishore, Sep 24 '15 at 05:14
`sql` is userdefined name .. please add your userdefined name over there — Krsna Kishore, Sep 24 '15 at 05:15
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx read the section on commandtimeout — Philip Stuyck, Sep 24 '15 at 05:20

Brendan Green · Accepted Answer · 2018-07-11T06:06:03.680

The issue is not the connection timeout (time taken to establish a connection to the database server), but rather the command timeout (time taken to execute the command).

In your DbUtil class, set the command timeout against the SqlCommand like so:

using (SqlCommand cmd = cn.CreateCommand())
{
    cmd.CommandTimeout = 120; //set timeout - use appropriate value
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = storedProcName;
    // assign parameters passed in to the command
    foreach (var procParameters in procParameter)
    {
        cmd.Parameters.Add(procParameters.Value);
    }
    rc = cmd.ExecuteNonQuery();
}

It would be worthwhile checking the performance of the stored procedure as well.

score 0 · Answer 2 · answered Sep 24 '15 at 05:18

0

As you are facing a timout on your command, therefore you need to increase the timout of your sql command. You can specify it in your command like this

// Setting command timeout to 2 minutes
cmd.CommandTimeout = 120;

answered Sep 24 '15 at 05:18

Krsna Kishore

8,233
4
32
48

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

2 Answers2