为什么我得到“超时”和“找不到表0”并采取什么预防措施？ [英] Why am I getting "timeout" and "Cannot find table 0" and what preventive measures are at my disposal?

问题描述

在上一个问题的答案这里，我被告知还要问这个相关的问题。

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.

第一个是超时过期。在操作完成或服务器之前经过的超时时间没有响应。

...以及之后快速跟随是，找不到表0

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

第一个exeption消息表示try部分的最后一行代码（new SqlDataAdapter cmd）.Fill（ds）;）在下面的方法中抛出异常：

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];
            }
        }
    }
}

第二个异常消息声称它从上面的方法（ 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 是一种Stored Proc，用于其他地方，在我努力之前和这里的活动，所以不是SP本身引起问题。

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];
            }
        }
    }
}

推荐答案

错误是因为填写或开放部分在这里

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.

超时可能发生的原因有很多。可能需要更多的信息，但如果您在管理工作室中使用sql profiler，您应该可以得到一个想法，如果连接打开，被调用，它尝试运行多长时间，以及导致超时的部分。如果它在其他地方工作，可以捕获您的输入参数，并直接尝试这些。

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.

同时设置超时值为0应该给您最大的可能。

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

这篇关于为什么我得到“超时”和“找不到表0”并采取什么预防措施？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！