Link to home
Start Free TrialLog in
Avatar of T Hoecherl
T HoecherlFlag for United States of America

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("Data 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.CommandText, cn)
        Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dataAdapter)
        Dim table = New DataTable
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        dataAdapter.Fill(table)
        PCTSNUGZFILTERBindingSource.DataSource = table
        PCT_SNUGZ_FILTERDataGridView.DataSource = PCTSNUGZFILTERBindingSource

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
Avatar of Randy Downs
Randy Downs
Flag of United States of America image

Assuming cmd is what's timing out your line below should give you infinite time to run the procedure.

cmd.CommandTimeout = 0

Avatar of T Hoecherl

ASKER

The line just before Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd.CommandText, cn) is:

cmd.CommandTimeout = 0

but I still timeout.
Can you add this below your DIM dataAdapter line?

dataAdapter.SelectCommand.CommandTimeout = 120
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
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you gerwinjansen.