VB.net/SQL timeout expired

T Hoecherl
T Hoecherl used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming cmd is what's timing out your line below should give you infinite time to run the procedure.

cmd.CommandTimeout = 0

T HoecherlDeveloper

Author

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

cmd.CommandTimeout = 0

but I still timeout.
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Can you add this below your DIM dataAdapter line?

dataAdapter.SelectCommand.CommandTimeout = 120
T HoecherlDeveloper

Author

Commented:
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?
Topic Advisor
Most Valuable Expert 2016
Commented:
Yes, it is in seconds. The default is 30 I believe. You can test by setting it to 10, 30 and 120 if you like.
I recommend to always set a value instead of 0 (zero), this way you will get notified in case of future performance degradation or database issues.
T HoecherlDeveloper

Author

Commented:
Thank you gerwinjansen.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial