I'm not sure if this is a VB.NET error or SQL Server. But I get the above error with following stack trace:
[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
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:
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?
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
Best Answer
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.
SQL Connection timeout is different from timeout set for the Command that is executed.
It is defined as follows. From MSDN
On setting the connection timeout.. from MSDN
Default value for all timeouts is 30 secs.