vb.net 超时已过。操作完成前超时时间已过或服务器未响应

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

提问by salvationishere

I'm not sure if this is a VB.NET error or SQL Server. But I get the above error with following stack trace:

我不确定这是 VB.NET 错误还是 SQL Server。但是我通过以下堆栈跟踪收到上述错误：

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
GlobalFunctions.GlobalF.GetComplaintTrendingList6(DateTime FirstMonth, DateTime LastMonth, Int32 rowLevel) +489
ASP.website_complaints_complainttrendinglist6_aspx.Main() in e:\inetpub\amdmetrics-d.web.abbott.com\wwwroot\Website\Complaints\ComplaintTrendingList6.aspx:94 ASP.website_complaints_complainttrendinglist6_aspx.Page_Load(Object Sender, EventArgs E) in e:\inetpub\amdmetrics-d.web.abbott.com\wwwroot\Website\Complaints\ComplaintTrendingList6.aspx:60 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

[SqlException (0x80131904): 超时已过期。操作完成之前超时时间已过或服务器没有响应。]
System.Data.SqlClient.SqlConnection.OnError(SqlException 异常，布尔中断连接) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException 异常，布尔breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSetbulkParserStateObject stateObj) +2O3b9ObjectStateser
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data .SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand. ExecuteDbDataReader(CommandBehavior 行为) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior 行为) +10
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords、String srcTable、IDbCommand 命令、CommandBehavior 行为）+130
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand 命令, CommandBehavior 行为) +287
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
GlobalFunctions.GlobalF.GetComplaintTrendingList6 (DateTime FirstMonth, DateTime LastMonth, Int32 rowLevel) +489
ASP.website_complaints_complainttrendinglist6_aspx.Main() 位于 e:\inetpub\amdmetrics-d.web.abbott.com\wwwroot\Website\Complaints\ComplaintTrendingList6.aspx:94 ASP.website_complaints_complainttrendinglist6_aspx.ender,eArgs:EArgs. inetpub\amdmetrics-d.web.abbott.com\wwwroot\Website\Complaints\ComplaintTrendingList6.aspx:60 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14 System.Web .Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50 System.Web.UI.Page .ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

This is on Microsoft .NET Framework Version:2.0.50727.3620; ASP.NET Version:2.0.50727.3618 and SQL Server 2008. The line it flags causing this error says:

这是在 Microsoft .NET Framework 版本：2.0.50727.3620 上；ASP.NET 版本：2.0.50727.3618 和 SQL Server 2008。它标记导致此错误的行说：

1: PrintMessageGrid.DataSource = GlobalFunctions.GlobalF.GetComplaintTrendingList6(FirstMonthDate, LastMonthDate, TheLevel) 

Even though I can run this stored procedure in Query Analyzer and it returns in 8 seconds. What could be the cause and fix?

即使我可以在查询分析器中运行这个存储过程并且它在 8 秒内返回。可能是什么原因和解决方法？

Here's more detail where I declare this function:

这是我声明此函数的更多详细信息：

Public Shared Function GetComplaintTrendingList6(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer) As DataSet
    Dim DSPageData As New System.Data.DataSet
    Dim param(2) As SqlClient.SqlParameter

    param(0) = New SqlParameter("@FirstMonthDate", SqlDbType.DateTime)
    param(0).Value = FirstMonth
    param(1) = New SqlParameter("@LastMonthDate", SqlDbType.DateTime)
    param(1).Value = LastMonth
    param(2) = New SqlParameter("@TheLevel", SqlDbType.Int)
    param(2).Value = rowLevel

    ''# A Using block will ensure the .Dispose() method is called for these variables, even if an exception is thrown 
    ''# This is IMPORTANT - not disposing your connections properly can result in an unrespsonsive database 
    Using conn As New SQLConnection(ConfigurationSettings.AppSettings("AMDMetricsDevConnectionString")), _
   cmd As New SQLCommand("ComplaintTrendingList6", conn), _
    da As New SQLDataAdapter(cmd)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddRange(param)

        da.Fill(DSPageData)
    End Using

    Return DSPageData
End Function

The strange thing is that if I make this small change in my stored proc, it passes, but doesn't give me the data I want: No error, but this version doesn't give me what I want:

奇怪的是，如果我在我的存储过程中进行这个小改动，它会通过，但没有给我我想要的数据：没有错误，但这个版本没有给我我想要的：

SELECT E.PRODUCT_GROUP, a.QXP_SHORT_DESC, COUNT(DISTINCT A.QXP_EXCEPTION_NO), A.QXP_REPORT_DATE, DATEADD(M, DATEDIFF(M, 0, A.QXP_REPORT_DATE), 0) AS STARTDATE
FROM ALL_COMPLAINTS A
LEFT OUTER JOIN SMARTSOLVE.V_QXP_ISSUE_REF D ON A.QXP_ID = D.IRF_QXP_ID 
INNER JOIN CT_ProductFailures b ON b.old_modes = a.qxp_short_desc
LEFT OUTER JOIN [MANUAL].PRODUCTS E ON A.EPA_PRD_CODE = E.LIST_NUMBER 
LEFT JOIN SMARTSOLVE.V_CXP_CUSTOMER_PXP C ON A.QXP_ID = C.QXP_ID
INNER JOIN @SelectedLevels F ON A.[LEVEL] = F.LevelId
WHERE   --[LEVEL] > 0 AND
(A.QXP_SHORT_DESC <> 'Design Control') 
and A.QXP_REPORT_DATE >= @OneYearAgo AND A.QXP_REPORT_DATE <= @LastMonthDate
AND (C.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR C.QXP_EXCEPTION_TYPE IS NULL)  
GROUP BY E.PRODUCT_GROUP, A.QXP_REPORT_DATE,  A.QXP_SHORT_DESC 

Error-causing stored proc:

导致错误的存储过程：

INSERT #PVAL_NUM
SELECT E.PRODUCT_GROUP, b.new_modes 'QXP_SHORT_DESC', COUNT(DISTINCT A.QXP_EXCEPTION_NO), A.QXP_REPORT_DATE, DATEADD(M, DATEDIFF(M, 0, A.QXP_REPORT_DATE), 0) AS STARTDATE
FROM ALL_COMPLAINTS A
LEFT OUTER JOIN SMARTSOLVE.V_QXP_ISSUE_REF D ON A.QXP_ID = D.IRF_QXP_ID 
INNER JOIN CT_ProductFailures b ON b.old_modes = a.qxp_short_desc
LEFT OUTER JOIN [MANUAL].PRODUCTS E ON A.EPA_PRD_CODE = E.LIST_NUMBER 
LEFT JOIN SMARTSOLVE.V_CXP_CUSTOMER_PXP C ON A.QXP_ID = C.QXP_ID
INNER JOIN @SelectedLevels F ON A.[LEVEL] = F.LevelId
WHERE   --[LEVEL] > 0 AND
(A.QXP_SHORT_DESC <> 'Design Control') 
and A.QXP_REPORT_DATE >= @OneYearAgo AND A.QXP_REPORT_DATE <= @LastMonthDate
AND (C.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR C.QXP_EXCEPTION_TYPE IS NULL)
GROUP BY E.PRODUCT_GROUP, A.QXP_REPORT_DATE, b.new_modes

采纳答案by Oded

What you get with the Query Analyzer (or SSMS, these days) and what you application gets can be very different things, mostly due to parameter sniffing.

您使用查询分析器（或 SSMS，现在）获得的内容与您的应用程序获得的内容可能非常不同，主要是由于参数嗅探。

I suggest reading this articleby Erland Sommarskog for a details.

我建议阅读Erland Sommarskog 的这篇文章以了解详细信息。

回答by user_v

You need to increase the command timeout on the select command of the dataadapter.

您需要在数据适配器的选择命令上增加命令超时。

Below is the code to increase the command timeout for dataadapter.

下面是增加 dataadapter 命令超时的代码。

adapter.SelectCommand.CommandTimeout = 0;

SQL Connection timeout is different from timeout set for the Command that is executed.

SQL 连接超时与为执行的命令设置的超时不同。

It is defined as follows. From MSDN

它的定义如下。来自 MSDN

Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

获取尝试建立连接时等待的时间，然后终止尝试并生成错误。

On setting the connection timeout.. from MSDN

关于设置连接超时..来自 MSDN

You can set the amount of time a connection waits to time out by using the ConnectTimeout or Connection Timeout keywords in the connection string. A value of 0 indicates no limit, and should be avoided in a ConnectionString because an attempt to connect waits indefinitely.

您可以使用连接字符串中的 ConnectTimeout 或 Connection Timeout 关键字来设置连接等待超时的时间。值 0 表示没有限制，应避免在 ConnectionString 中使用，因为连接尝试会无限期地等待。

Default value for all timeouts is 30 secs.

所有超时的默认值为 30 秒。

回答by Rob P.

I believe that means the database operation took too long and it gave up.

我相信这意味着数据库操作花费了太长时间而放弃了。

Try adding a Connect Timeout in the web.config

尝试在 web.config 中添加连接超时

<add key="DBConnection" value="server=LocalHost;uid=sa;pwd=;database=DataBaseName;Connect Timeout=200; pooling='true'; Max Pool Size=200"/>

回答by E-r Gabriel Doronila

"..database operation took too long and it gave up."

“..数据库操作时间太长，它放弃了。”

This is correct. This happens when there's a conflict with your connection to the server.

这是对的。当您与服务器的连接发生冲突时会发生这种情况。

To temporarily resolve this..

暂时解决这个..

"Repair your LAN connection"

“修复您的 LAN 连接”

You can do this by right-clicking on your connection and clicking "REPAIR"

您可以通过右键单击您的连接并单击“修复”来执行此操作

To permanently resolve this..

为了永久解决这个..

There shouldn't be a combination of static and dynamic ip addresses in your workgroup. If you want to set a static group, do not include dynamic connections - vice versa.

您的工作组中不应有静态和动态 IP 地址的组合。如果要设置静态组，请不要包含动态连接 - 反之亦然。

After that, there shouldn't be an error displaying "Timeout Expired" anymore.

之后，不应再出现显示“超时已过期”的错误。

cheers!

干杯！