VB.Net - Connection Error Timing Out

can you show the code that you are using to get this error?

Hello There...thanks for replying.  That was my next thing to figure out.  
I am running this as an exe.  If I put a breakpoint in the Catch, is there a way to get back to the code that caused the error?

Thanks,
jimbo99999

I can run it from the project to try and find the issue.

I was more thinking about the vb.net code you are using to run the query/open the connection.

if you want to see the real query that is sent to SQL Server, better run SQL Server Profiler to catch it.

Here is how I structure my query running and connections:

Dim ShifalikaConnectSub As SqlConnection
Dim ShifalikaConnectSubConnString As String = "SERVER=ITS-SQL;DATABASE=Shifalika;UID=ITSdbADMIN;PWD=PTAreports"
ShifalikaConnectSub = New System.Data.SqlClient.SqlConnection(ShifalikaConnectSubConnString)
ShifalikaConnectSub.Open()
Dim SubscriptValue As String = ""
Dim SubscriptCounter As Integer = 0
Dim cmdGetSubscript As New SqlCommand("SELECT " & _
 "SubscriptValue " & _
  "FROM [BorderfreeExportSubscript] with (nolock) " & _
   "WHERE [ExportDate]= '" & Date.Now.Date & "'", ShifalikaConnectSub)
   cmdGetSubscript.CommandTimeout = 700
Dim rdrGetSubscript As SqlDataReader = cmdGetSubscript.ExecuteReader
If rdrGetSubscript.HasRows Then
      rdrGetSubscript.Read()
      SCACctr = rdrGetSubscript.Item("SubscriptValue")
 End If
 ShifalikaConnectSub.Close()

you are getting a time out on this query? Do you have the full text of the exception?

Try setting you CommandTimeOut to 0:

cmdGetSubscript.CommandTimeout = 0

Select allOpen in new window

The problem I have is not knowing which one is giving me the timeout error. I am stepping through the code now to try and figure out which Invoice I am running when it errors.  
I took off the timeout designation on my connections and left the settings on my commands.  That reference I gave you was show my code style.  That one is not resource intensive at all.  But the others one from our Client Table I have a command timeout set to 1500.  

See, I am just not sure how to best set those.  No timeout setting on the connection and a timeout on the command?

I have a breakpoint on my catch. When it errors how do I get back to the last line executed?

Thanks,
jimbo99999

>>When it errors how do I get back to the last line executed?

there is no easy way. but you can:
-use the SQL profiler to find which statement are sent to the database server
-place a try...catch around each command execution so that way you will be able to find which one is causing the problem

I took off all of the connection timeouts and set all the command timeouts to 0 and one of them still failed!!!

-place a try...catch around each command execution so that way you will be able to find which one is causing the problem

- ok, I will try that for this afternoons run and get to the bottom of this
- just bewildered after taking all timeouts off of commands and connections

Good Day

- I have placed a Try/Catch around each command execution and am receiving an error

Operator '+' is not defined for type 'Double' and type 'DBNull'

Dim rdrGetConInfo As SqlDataReader
Try
     rdrGetConInfo = cmdGetConInfo.ExecuteReader
Catch ex As Exception
     Dim Test As String = "Help"
End Try
'Dim rdrGetConInfo As SqlDataReader = cmdGetConInfo.ExecuteReader

Do you by chance see anything wrong that may be the source of my new error?

Thanks,
jimbo99999

you have an issue with your query then. Something in your query is trying to add a null value.

Ok, I narrowed down the timeout issue.  It was on an Update command.  I did not have any timeout settings on it.  I continued to retry and it worked a few minutes later pretty quickly.

It "feels" like there may be an issue in the database. Do you by chance have any suggestions I can check on?

Thanks,
jimbo99999

Have you been able to find an exception?

This is the exception I am getting "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated."

I did find 2 queries that were slower that had two common fields with the failing update.  
We added indexes on those 2 fields.  I will monitor on this afternoon run.

Thanks,
jimbo99999

how many rows are these commands updating? how many rows total in the table? Is the command as JOINs? Can we see one of the query?

UPDATE [Processing Table] SET
[Last Updated] = getDate()
WHERE (Case(SCAC) when '23250' then [Audit Reason 4] Else [Premium Invoice Number] END) = '" & CarrierInvoiceNumber & "'
AND [SCAC] = '" & SCAC & "'"

- We put an index on [Audit Reason 4] and [Premium Invoice Number] a few days ago.  
- This command updates at the most 1,000 rows
- Currently about 162,000 records in the table

I was just running the process. When stepping through and it timesout I can hit F10 and it executes.  But if I would run by the exe it would throw the error.  Is there a way to do a resume so it keeps trying?  If I put a timeout of 0 on the command will that make it keep re-trying?

Thanks again for your help,
Ed

Ok, good idea there...perhaps resources are being used on that Case.  I have a run to do this afternoon, pending records are ready, to try your idea.

I did not get a timeout error after processing 3 times with the statement separated.

Should I set the timeout on the command = 0 or will not having one wait longer?

Thanks

I wouldn't set the TimeOut to at all (keeping the default value)

Ok...I have them off.  I have a co-worker running via the exec while I am on vacation this week.  I will meet with her when arriving back and see about any performance issues experienced.

Please don't close the question as I will be back Monday from vacation.

Thanks,
Jimbo99999

We recently found that another process that runs throughout the day was blocking the table I am trying to update.  

I learned from Eric's feedback so I am awarding the points.

Thanks again,
jimbo99999