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

Question

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

Answer 1

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.

adapter.SelectCommand.CommandTimeout = 0;

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

It is defined as follows. From 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

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.

Default value for all timeouts is 30 secs.

Answer 2

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

Try adding a Connect Timeout in the web.config

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