T Hoecherl
asked on
VB.net/SQL timeout expired
I have a VB.net application that executes an SQL stored procedure. The stored procedure includes many table joins and it inserts data into a temporary table. The last line in the stored procedure is a select query to display the data in the temporary table.
The relevant VB.net code is:
Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("D ata Source = SNUGZGP.SNUGZ.com;Initial Catalog = SNUGZ;Persist Security Info= True;Connection Timeout = 0;User ID=sa;Password=******")
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
cmd.CommandText = "EXEC udp_ROUTING_SELECTION '" & WorkCenter.strWorkCtr & "', " & intWCDisplay & ", '" & strMachine & "'"
cmd.CommandTimeout = 0
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd.Command Text, cn)
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dataAdap ter)
Dim table = New DataTable
table.Locale = System.Globalization.Cultu reInfo.Inv ariantCult ure
dataAdapter.Fill(table)
PCTSNUGZFILTERBindingSourc e.DataSour ce = table
PCT_SNUGZ_FILTERDataGridVi ew.DataSou rce = PCTSNUGZFILTERBindingSourc e
With disturbing frequency, we get an error message on the dataAdapter.Fill(table) command.
The text of the error is:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I have attached a Word document with the screenshot of the error. The remote query timout limit on the SQL server is set to 0. The stored procedure, when executed in Management Studio, takes 15 to 25 seconds to execute.
What options are available to me to eliminate this error?
T
TimeoutErrorScreenshot.docx
The relevant VB.net code is:
Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("D
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
cmd.CommandText = "EXEC udp_ROUTING_SELECTION '" & WorkCenter.strWorkCtr & "', " & intWCDisplay & ", '" & strMachine & "'"
cmd.CommandTimeout = 0
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd.Command
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dataAdap
Dim table = New DataTable
table.Locale = System.Globalization.Cultu
dataAdapter.Fill(table)
PCTSNUGZFILTERBindingSourc
PCT_SNUGZ_FILTERDataGridVi
With disturbing frequency, we get an error message on the dataAdapter.Fill(table) command.
The text of the error is:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
I have attached a Word document with the screenshot of the error. The remote query timout limit on the SQL server is set to 0. The stored procedure, when executed in Management Studio, takes 15 to 25 seconds to execute.
What options are available to me to eliminate this error?
T
TimeoutErrorScreenshot.docx
ASKER
The line just before Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd.Command Text, cn) is:
cmd.CommandTimeout = 0
but I still timeout.
cmd.CommandTimeout = 0
but I still timeout.
Can you add this below your DIM dataAdapter line?
dataAdapter.SelectCommand. CommandTim eout = 120
dataAdapter.SelectCommand.
ASKER
Wow, gerwinjansen! That seems to have fixed the problem. I will need to deploy the change to some of the other workstations and have them get on the program simultaneously, but this is the first promising break. Is 120 in units of seconds?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you gerwinjansen.
cmd.CommandTimeout = 0