I'm receiving this error:
Timeout Expired
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
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.