我不确定这是VB.NET错误还是SQL Server错误。但我在以下堆栈跟踪中遇到了上述错误:
[SqlException (0x80131904): 超时。在操作完成之前超时时间已过或服务器没有响应。]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
这是在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)
即使我可以在查询分析器中运行此存储过程并在8秒钟内返回结果,但是可能的原因和解决方法是什么?
下面是声明此函数的更多详细信息:
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
奇怪的是,如果我在存储过程中进行了这个小改变,它会通过,但却不能给我想要的数据: 没有错误,但这个版本并不能给我想要的数据:
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
引发错误的存储过程:
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