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
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dataAdap
Dim table = New DataTable
table.Locale = System.Globalization.Cultu
ce = table
rce = PCTSNUGZFILTERBindingSourc
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?