Cannot set CommandTimeout to longer than 90 seconds

Question

I have an asp.net web app written with C# code behind in Visual Studio 2008.

I have a SQL query that queries a SQL Server database on another server. When I run the query it times out after 90 seconds. I have tried all sorts of different settings.

I've trawled the internet but still cannot find an answer. I have a line in my code to set CommandTimeout for the query. If I set it to CommandTimeout = 1; the query will time out after 1 second, if I set it to CommandTimeout = 90; the query will timeout after 90 seconds.

This is all good but my query takes approx. 150 seconds to run. If I change the code to CommandTimeout = 200; the query still times out after 90 seconds. It seems I can only change the timeout when it is less than 90 seconds. Anything above 90 seconds still times out at 90 seconds.

This is driving me mad. Is there another setting somewhere that is overriding my code?

Here is my code

// bind the data to the Gridview
private void BindTaskList()
{
    string startDate = StartDate.Text;
    string endDate = EndDate.Text;

    // Create a connection string referring to the connection string from web.config file
    string conStr = ConfigurationManager.ConnectionStrings["Docupro_ReportingConnectionString"].ConnectionString;

    SqlConnection sqlConnection = new SqlConnection(conStr);

    // This is the SQL query and must be in one long line
    SqlCommand sqlCommand = new SqlCommand("SELECT T5.DisplayName AS 'User', T2.LongName AS 'Print Type', SUM(T1.Quantity) AS 'Total Quantity', '£'+CONVERT(varchar, SUM(T1.Amount), 3) AS 'Total Cost' FROM tblTransaction T1 JOIN tblItem T2 ON T1.ItemID = T2.ItemID JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID JOIN tblTender T4 ON T1.TenderID = T4.TenderID JOIN tblCustomer T5 ON T4.CustomerID = T5.CustomerID JOIN tblTerminal T6 on T1.TerminalID = T6.TerminalID JOIN tblStation t7 on T6.StationID = t7.StationID WHERE (TransactionDateTime BETWEEN @StartDate AND @EndDate)AND T3.LongName = 'Not Assigned' GROUP BY T5.DisplayName, T2.LongName ORDER BY T5.DisplayName", sqlConnection);

    // Create the parameters from the text boxes and drop down list
    sqlCommand.Parameters.Add(new SqlParameter("@StartDate", startDate));
    sqlCommand.Parameters.Add(new SqlParameter("@EndDate", endDate));

    // Set the command timeout to 200 seconds to allow for long queries
    sqlCommand.CommandTimeout = 200;
    sqlConnection.Open();

    // Create a DataSet to fill with data
    SqlDataAdapter myAdapter = new SqlDataAdapter(sqlCommand);
    DataSet myDataSet = new DataSet();
    myAdapter.Fill(myDataSet);

    // Turn off GridView Footer
    GridView1.ShowFooter = false;

    // Fill the GridView with the DataSet
    GridView1.DataSource = myDataSet;
    GridView1.DataBind();
}

Many thanks in anticipation

Andy

Error message is:

Sys.WebForms.PageRequestManagerTimeoutException: The server request timed out ScriptResource.axd
Code:0

Answer 1

The error message shows that the timeout comes from ASP.NET (not from ADO.NET). Set Server.ScriptTimeout=200.

Interpreting the error message is the first step to debugging any error. Don't just stop when you read "timeout". Read and interpret everything.

Answer 2

FWITW...

I had an error (not ASP related -this was within a console application) and I found that setting the CommandTimeout after setting any Parameters didn't seem to take effect (even though the property was updated.)

eg

SqlCommand comm = new SqlCommand(conn, proc);
comm.Parameters.AddWithValue("@a",123);
comm.CommandTimeout = 300;
comm.ExecuteReader(); //Times out at 30 seconds

but

SqlCommand comm = new SqlCommand(conn, proc);
comm.CommandTimeout = 300;
comm.Parameters.AddWithValue("@a",123);
comm.ExecuteReader(); //Times out at 300 seconds

I did actually have a function returning the SqlCommand and then it was executed when returned, but I am guessing this simplified version would have the same result.

In summary

Try setting the CommandTimeout property immediately after newing-up the SqlCommand.