c# - Why am I getting "timeout" and "Cannot find table 0" and what preventive measures are at my disposal?

Question

In the answer to a previous question here, I was advised to also ask about this related issue.

Once in awhile, the report generation code I have throws two exceptions (they don't display to the user, and they think everything is hunky dory), but I get them emailed to me.

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

...and the one that always follows quickly thereafter is, "Cannot find table 0"

The first exeption message opines that the last line of code in the try section ("new SqlDataAdapter(cmd).Fill(ds);") in the method below is throwing the exception:

public static DataTable ExecuteSQLReturnDataTable(string sql, 
    CommandType cmdType, params SqlParameter[] parameters)
{
    using (var ds = new DataSet())
    {
        using (var connStr = new SqlConnection(CPSConnStr))
        {
            using (var cmd = new SqlCommand(sql, connStr))
            {
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = EXTENDED_TIMEOUT;
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }

                try
                {
                    cmd.Connection.Open();
                    new SqlDataAdapter(cmd).Fill(ds);
                }
                catch (Exception ex)
                {
                    RoboReporterConstsAndUtils.HandleException(ex);
                }
                return ds.Tables[0];
            }
        }
    }
}

The second exception message claims that it emanates from the last significant line in the method above ("return ds.Tables[0];") as well as this one:

var dtFillRateResults =
    RoboReporterSQL.ExecuteSQLReturnDataTable
       (FILL_RATE_BY_DISTRIBUTOR_BY_CUSTOMER_STORED_PROC,
        CommandType.StoredProcedure,
            new SqlParameter()
            {
                ParameterName = "@Unit",
                SqlDbType = SqlDbType.VarChar,
                Value = _unit
            },
            new SqlParameter()
            {
                ParameterName = "@Member",
                SqlDbType = SqlDbType.VarChar,
                Value = _memberId
            },
            new SqlParameter()
            {
                ParameterName = "@BegDate",
                SqlDbType = SqlDbType.DateTime,
                Value = Convert.ToDateTime(_dateBegin)
            },
            new SqlParameter()
            {
                ParameterName = "@EndDate",
                SqlDbType = SqlDbType.DateTime,
                Value = Convert.ToDateTime(_dateEnd)
            }
    );

FILL_RATE_BY_DISTRIBUTOR_BY_CUSTOMER_STORED_PROC is a Stored Proc that is used elsewhere and prior to my efforts and activities here, so it's not the SP itself that's causing the problem.

For the super-curious, the bespoke method call from the code above is:

public static DataTable ExecuteSQLReturnDataTable(string connectionStr,     
    string sql, CommandType cmdType, params SqlParameter[] parameters)
{
    using (var ds = new DataSet())
    {
        using (var connStr = new SqlConnection(connectionStr))
        {
            using (var cmd = new SqlCommand(sql, connStr))
            {
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = EXTENDED_TIMEOUT;
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }

                try
                {
                    cmd.Connection.Open();
                    new SqlDataAdapter(cmd).Fill(ds);
                }
                catch (Exception ex)
                {
                    RoboReporterConstsAndUtils.HandleException(ex);
                    return null;
                }
                return ds.Tables[0];
            }
        }
    }
}

Answer 1

The errors are because the Fill or the open part here

try
{
    cmd.Connection.Open();
    new SqlDataAdapter(cmd).Fill(ds);
}

is timing out but since you handle the exception the execution ends up hitting the return statement where you are trying to return a table from the still null table collection of the dataset.

The timeout can happen for many reasons. Probably need more info for that but if you use the sql profiler in management studio you should be able to get an idea on if the connection opens, is proc called, how longs its trying to run and what part is causing the timeout. If it works elsewhere capture your input params and try those out directly.

Also setting a timeout of 0 should give you the max possible.