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

Question

When I run my code I get the following exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

My code is the following:

    private void FillInDataGrid(string SQLstring)
    {
        string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString; //hier wordt de databasestring opgehaald
        SqlConnection myConnection = new SqlConnection(cn);
        SqlDataAdapter dataadapter = new SqlDataAdapter(SQLstring, myConnection);
        DataSet ds = new DataSet();
        myConnection.Open();
        dataadapter.Fill(ds, "Authors_table");
        myConnection.Close();
        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "Authors_table";
    }

And my SQLstring is the following:

SELECT dbo.[new].[colom1],dbo.[new].[colom2],dbo.[new].[colom3],dbo.[new].[colom4],  
                dbo.[new].[Value] as 'nieuwe Value',
                dbo.[old].[Value] as 'oude Value'
                FROM dbo.[new]
                JOIN dbo.[old] ON dbo.[new].[colom1] = dbo.[old].[colom1] and dbo.[new].[colom2] = dbo.[old].[colom2] and dbo.[new].[colom3] = dbo.[old].[colom3] and dbo.[new].[colom4] = dbo.[old].[colom4] 
                where dbo.[new].[Value] <> dbo.[old].[Value]

@EhsanSajjad Do you think the time executing is the problem here? — wouter de jong, Dec 23 '16 at 10:24
@EhsanSajjad I am connecting to that same string earlier, and that works — wouter de jong, Dec 23 '16 at 10:27
Clearly there is a timeout during execution. May be the number of rows, the joins, the conditions you have in the joins. See this http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation?rq=1 — bit, Dec 23 '16 at 10:30
@EhsanSajjad and bit You guys were indeed right, I made the table even smaller, and now it executed — wouter de jong, Dec 23 '16 at 10:43
Is there a specific requirement where you're needing to check equality between every single column of both tables in your JOIN section? — Zephire, Dec 23 '16 at 11:05
@EhsanSajjad I think the question is not duplicate. One is for [sql Command](https://stackoverflow.com/questions/tagged/sqlcommand) And another is for [SqlDataAdapter](https://stackoverflow.com/questions/tagged/sqldataadapter) . As both are from System.Data.SqlClient namespace so the error message is same. But solution way is different. — Muhammad Ashikuzzaman, Jan 17 '19 at 05:52
@MuhammadAshikuzzaman setting the query timeout is same in either case — Ehsan Sajjad, Jan 17 '19 at 06:04
@EhsanSajjad one is SqlDataAdapter da = new SqlDataAdapter(str, dbCon); da.SelectCommand.CommandTimeout = 300; and another is SqlCommand command = new SqlCommand(); command.CommandTimeout = 300; — Muhammad Ashikuzzaman, Jan 17 '19 at 09:07

score 17 · Accepted Answer · edited May 23 '17 at 10:30

If your query needs more than the default 30 seconds, you might want to set the CommandTimeout higher. To do that you'll change it after you instantiated the DataAdapter on the SelectCommand property of that instance, like so:

private void FillInDataGrid(string SQLstring)
{
    string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString; //hier wordt de databasestring opgehaald
    DataSet ds = new DataSet();
    // dispose objects that implement IDisposable
    using(SqlConnection myConnection = new SqlConnection(cn))
    {
        SqlDataAdapter dataadapter = new SqlDataAdapter(SQLstring, myConnection);

        // set the CommandTimeout
        dataadapter.SelectCommand.CommandTimeout = 60;  // seconds

        myConnection.Open();
        dataadapter.Fill(ds, "Authors_table"); 
    }
    dataGridView1.DataSource = ds;
    dataGridView1.DataMember = "Authors_table";
}

The other option is to address your query. In Sql Server you can analyze the execution plan. I bet there is a full-table scan in it. You might experiment with adding an index on one or two columns in your [old] and [new] table. Keep in mind that adding indexes comes at the cost of higher execution times for inserts and updates and space requirements.

I tried 60 seconds, but it still didnt load, im going to try 15minutes now, is there any way to let the process speed up? besides changing my cpu. — wouter de jong, Dec 23 '16 at 11:16
As I said, your query is the problem. You might try adding indexes. And I would simply run that statement in Sql Server management studio and optimize there based on the outcome of the execution plan. — rene, Dec 23 '16 at 11:29
After an hour it still wasnt done :P I am now executing the query in de SQL server management studio which already takes 5mins haha — wouter de jong, Dec 23 '16 at 12:28

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

1 Answers1