Timeout Expired (cmd.CommandTimeout = 0 being ignored)

Question

I'm receiving a Timeout Expired Error Even After Setting the cmd.CommandTimeout = 0 The Query is pulling from a SAP database, in SAP the sql query runs fine however when I try run it on an adapter in visual studio I get that Timeout Expired error. Is there something I am missing? How do I extend the timeout period so my query can run? Here's my VB.Net code:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  Dim ds As New DataSet
  Dim dv As New DataView
  Dim ConStr As String = "Data Source= .\SQLOLEDB.1;Password=Password;Persist Security Info=True;User ID=User;Initial Catalog=XXX;Data Source=xxx.xxx.x.x;Connection Timeout=0"
  Dim cn As SqlConnection
  Try
      cn = New SqlConnection(ConStr)
      cn.Open()
      Dim cmd = cn.CreateCommand()
      cmd.CommandTimeout = 0
      Dim ad As New SqlDataAdapter("/* SELECT FROM [dbo].[OITM] T2 */" &
                                        " DECLARE @Whs_From AS VARCHAR(10)" &
                                        " DECLARE @Whs_To AS VARCHAR(10)" &
                                        "/* WHERE */" &
                                        " SET @Whs_From = /* T2.DfltWH */ 'AUT-L-N'" &
                                        " SET @Whs_To = /* T2.DfltWH */ 'AUT-L-N'" &
                                        " SELECT    QUT1.[ItemCode] AS 'Code'," &
                                        " ISNULL(SUM(QUT1.[OpenQty]), 0) AS 'Open'" &
                                        " INTO  #QuoteSum" &
                                        " FROM  QUT1 QUT1 INNER JOIN OITM OITM ON QUT1.[ItemCode] = OITM.[ItemCode] INNER JOIN OQUT OQUT ON QUT1.[DocEntry] = OQUT.[DocEntry]" &
                                        " WHERE     QUT1.[WhsCode] = OITM.[DfltWH] AND QUT1.[LineStatus] = 'O' AND OQUT.[Canceled] != 'Y' GROUP BY QUT1.[ItemCode]" &
                                        " SELECT    QUT1.[ItemCode] AS 'Code'," &
                                        " SUM(QUT1.[quantity]) AS 'Last360'" &
                                        " INTO  #Last360" &
                                        " FROM  QUT1 QUT1 INNER JOIN OITM OITM ON QUT1.[ItemCode] = OITM.[ItemCode] INNER JOIN OQUT OQUT ON QUT1.[DocEntry] = OQUT.[DocEntry]" &
                                        " WHERE     QUT1.[WhsCode] = OITM.[DfltWH] AND CONVERT(CHAR, QUT1.[DocDate], 112) < CONVERT(CHAR, GetDate()-270, 112) AND CONVERT(CHAR, QUT1.[DocDate], 112) >= CONVERT(CHAR, GetDate()-360, 112) AND OQUT.[Canceled] != 'Y'" &
                                        " GROUP BY QUT1.[ItemCode]" &
                                        " SELECT    OITW.[WhsCode] AS 'Warehouse'," &
                                        " OITW.[ItemCode] AS 'Item Code'," &
                                        " OITM.[ItemName] AS 'Item Description'," &
                                        " ISNULL(QuoteSum.[Open], 0) AS 'Sales Quote Qty'," &
                                        " OITW.[IsCommited] AS 'Sales Order Qty'," &
                                        " (ISNULL(QuoteSum.[Open], 0) + OITW.[IsCommited]) AS 'Demand'," &
                                        " OITW.[OnHand] AS 'In Stock Qty', OITW.[OnOrder] AS 'Purchase Order Qty'," &
                                        " (OITW.[OnHand] + OITW.[OnOrder]) AS 'Available'," &
                                        " OITW.[OnHand] - OITW.[IsCommited] - ISNULL(QuoteSum.[Open], 0) + OITW.[OnOrder] AS 'To Manufacture/Buy'," &
                                        " Last360.[Last360] as 'Last 271-360'" &
                                        " FROM  OITW OITW LEFT OUTER JOIN #QuoteSum QuoteSum ON OITW.[ItemCode] = QuoteSum.[Code] INNER JOIN OITM OITM ON OITW.[ItemCode] = OITM.[ItemCode] AND OITW.[WhsCode] = OITM.[DfltWH] LEFT OUTER JOIN #Last360 Last360 ON OITW.[ItemCode] = Last360.[Code]" &
                                        " WHERE     OITM.[DfltWH] >=@Whs_From AND OITM.[DfltWH] <= @Whs_To" &
                                        " BEGIN DROP TABLE #QuoteSum END" &
                                        " BEGIN DROP TABLE #Last360 END;", cn)
      ad.Fill(ds, "OITM")
      dv.Table = ds.Tables("OITM")
      Me.DataGridView1.DataSource = dv
  Catch ex As Exception
      MsgBox(ErrorToString)
  End Try
End Sub

Here is the exact error message I am getting:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at CR_Test.Form1.Form1_Load(Object sender, EventArgs e) in c:\users\leroif\documents\visual studio 2013\Projects\CR Test\CR Test\Form1.vb:line 46

Can you post the exact error message along with the error stack trace? — sstan

sstan sstan · Accepted Answer · 2015-07-15T13:14:45

The problem is that you are overriding the timeout value on the wrong object.

cmd.CommandTimeout = 0

But you don't actually use the cmd object anywhere after that. It doesn't look like you even need to create a command object at all. Your query is performed using the ad SqlDataAdapter instance, not the cmd instance.

What you need to do is override the timeout value for the SqlDataAdapter.

I've never tried it myself, but apparently, this is how it's done:

ad.SelectCommand.CommandTimeout=0

Give that a try.

Timeout Expired (cmd.CommandTimeout = 0 being ignored)

2 Answers