title: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
body: <p>I'm not sure if this is a VB.NET error or SQL Server.  But I get the above error with following stack trace:</p>
  
  <blockquote>
    <p>[SqlException (0x80131904): Timeout
    expired.  The timeout period elapsed
    prior to completion of the operation
    or the server is not responding.]<br>
    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<br>
    System.Data.SqlClient.TdsParser.Run(RunBehavior
    runBehavior, SqlCommand cmdHandler,
    SqlDataReader dataStream,
    BulkCopySimpleResultSet
    bulkCopyHandler, TdsParserStateObject
    stateObj) +2392<br>
    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<br>
    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<br>
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
    cmdBehavior, RunBehavior runBehavior,
    Boolean returnStream, String method)
    +32    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
    behavior, String method) +141<br>
    System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
    behavior) +12<br>
    System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
    behavior) +10<br>
    System.Data.Common.DbDataAdapter.FillInternal(DataSet
    dataset, DataTable[] datatables, Int32
    startRecord, Int32 maxRecords, String
    srcTable, IDbCommand command,
    CommandBehavior behavior) +130<br>
    System.Data.Common.DbDataAdapter.Fill(DataSet
    dataSet, Int32 startRecord, Int32
    maxRecords, String srcTable,
    IDbCommand command, CommandBehavior
    behavior) +287<br>
    System.Data.Common.DbDataAdapter.Fill(DataSet
    dataSet) +94<br>
    GlobalFunctions.GlobalF.GetComplaintTrendingList6(DateTime
    FirstMonth, DateTime LastMonth, Int32
    rowLevel) +489<br>
    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<br>
    System.Web.UI.Control.OnLoad(EventArgs
    e) +99<br>
    System.Web.UI.Control.LoadRecursive()
    +50    System.Web.UI.Page.ProcessRequestMain(Boolean
    includeStagesBeforeAsyncPoint, Boolean
    includeStagesAfterAsyncPoint) +627</p>
  </blockquote>
  
  <p>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:</p>
  
  <pre><code>1: PrintMessageGrid.DataSource = GlobalFunctions.GlobalF.GetComplaintTrendingList6(FirstMonthDate, LastMonthDate, TheLevel) 
  </code></pre>
  
  <p>Even though I can run this stored procedure in Query Analyzer and it returns in 8 seconds.  What could be the cause and fix? </p>
  
  <p>Here's more detail where I declare this function:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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] &gt; 0 AND
  (A.QXP_SHORT_DESC &lt;&gt; 'Design Control') 
  and A.QXP_REPORT_DATE &gt;= @OneYearAgo AND A.QXP_REPORT_DATE &lt;= @LastMonthDate
  AND (C.QXP_EXCEPTION_TYPE &lt;&gt; 'Non-Diagnostic' OR C.QXP_EXCEPTION_TYPE IS NULL)  
  GROUP BY E.PRODUCT_GROUP, A.QXP_REPORT_DATE,  A.QXP_SHORT_DESC 
  </code></pre>
  
  <p>Error-causing stored proc:</p>
  
  <pre><code>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] &gt; 0 AND
  (A.QXP_SHORT_DESC &lt;&gt; 'Design Control') 
  and A.QXP_REPORT_DATE &gt;= @OneYearAgo AND A.QXP_REPORT_DATE &lt;= @LastMonthDate
  AND (C.QXP_EXCEPTION_TYPE &lt;&gt; 'Non-Diagnostic' OR C.QXP_EXCEPTION_TYPE IS NULL)
  GROUP BY E.PRODUCT_GROUP, A.QXP_REPORT_DATE, b.new_modes
  </code></pre>