超时已过期(cmd.CommandTimeout=0被忽略)

Question

我收到这个错误：

超时

即使在设置了CCD_ 1之后。

查询是从SAP数据库中提取的，在SAP中，SQL查询运行良好，但当我尝试在visual studio中的适配器上运行它时，我会收到Timeout Expired错误。

我有什么东西不见了吗？如何延长超时时间以便运行查询？

这是我的VB.Net代码：

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

以下是我收到的确切错误消息：

系统。数据SqlClient。SqlException：超时已过期。超时在操作完成之前经过的时间，或者服务器没有响应。在系统数据SqlClient。SqlConnection。OnError(SqlException异常，布尔breakConnection(系统数据SqlClient。TdsParser。ThrowExceptionAndWarning(TdsParserStateObjectstateObj(。数据SqlClient。TdsParser。运行(运行行为runBehavior，SqlCommand cmdHandler，SqlDataReader数据流，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObjectstateObj(。数据SqlClient。SqlDataReader。消费元数据((在系统中。数据SqlClient。位于的SqlDataReader.get_MetaData((系统数据SqlClient。SqlCommand。FinishExecuteReader(SqlDataReader ds，RunBehavior RunBehavior，字符串resetOptionsString(系统数据SqlClient。SqlCommand。RunExecuteReaderTds(命令行为cmdBehavior，RunBehavior RunBehavior、Boolean returnStream、Boolean异步(系统数据SqlClient。SqlCommand。RunExecuteReader(CommandBehaviorcmdBehavior，RunBehavior运行行为，布尔返回流，字符串方法，DbAsyncResult结果(系统数据SqlClient。SqlCommand。RunExecuteReader(CommandBehaviorcmdBehavior，RunBehavior运行行为，布尔返回流，字符串方法(系统数据SqlClient。SqlCommand。ExecuteReader(命令行为行为，字符串方法(系统数据SqlClient。SqlCommand。ExecuteDbDataReader(命令行为行为(系统数据常见的DbCommand。系统数据IDb命令。ExecuteReader(命令行为行为(。数据常见的DbDataAdapter。FillInternal(数据集dataset，DataTable[]datatables，Int32 startRecord，Int32 maxRecords，字符串srcTable、IDbCommand命令、CommandBehavior行为(系统数据常见的DbDataAdapter。填充(数据集数据集，Int32startRecord，Int32 maxRecords，字符串srcTable，IDbCommand命令，CommandBehavior行为(系统数据常见的DbDataAdapter。填充(数据集数据集，字符串srcTable(。Form1.Form1_Load(对象发送方，EventArgs e(在c:\users\leroif\documents\visual studio 2013\Projects\CR Test\CR中Test\Form1.vb:第46行

Answer 1

问题是覆盖了错误对象的超时值。

cmd.CommandTimeout = 0

但是在那之后的任何地方都不会实际使用cmd对象。看起来根本不需要创建命令对象。您的查询是使用ad SqlDataAdapter实例执行的，而不是使用cmd实例执行的。

您需要做的是覆盖SqlDataAdapter的超时值。

我自己从来没有尝试过，但很明显，它就是这样做的：

ad.SelectCommand.CommandTimeout=0

试试看。

Answer 2

将以下代码放在Try语句之前，这样它就不会忽略

 Dim cmd = cn.CreateCommand()
 cmd.CommandTimeout = 0