Timeout when I call a stored procedure in SQL Server 2008

Question

From C# with EF, I call a long stored procedure with ExecuteStoreCommand

30 sec after the procedure starts, I have a timeout exception.

How can I configure timeout ? On the server or in my C# client ?

Thanks

Answer 1

You could set the CommandTimeout on the underlying connection, but a much, much, much better idea would be to take the time and effort to diagnose why the timeout is happening in the first place.

Even if you "solve" the problem by upping your CommandTimeout, you could potentially be causing other blocking issues in your database. Look for blocking queries or poor query plans, or badly designed tables and indexes instead.

Answer 2

Anything you do, other than fixing the SQL in the stored procedure is just masking the real problem (the SQL).

You need to ask a question about speeding up your procedure, where you post your tables, and the stored procedure code so that it can be fixed once and for all.

Answer 3

using (var conn = new SqlConnection(ConnectionStrings.toMyDB))
{
    conn.Open();
    using (var cmd = new SqlCommand("myProc", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 30; // Increase this to allow the proc longer to run
        cmd.Parameters.AddWithValue("@Param", myParam);
        cmd.ExecuteNonQuery();
    }
}