VB.Net - Connection Error Timing Out

Good Day Experts!

Today I am having trouble with my project.  I have had some issues noted when there are larger amounts of data to process.  My error indicates: "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  The statement has been terminated."

Perhaps I am thinking of the timeout incorrectly.  On my connection time out feature I put 1500. On my command timeout I also put 1500.  I thought 1500 is seconds but the processing fails well before 25minutes.

Am I using this wrong? There is quite a bit of data that I need to process today.  I have seen while researching to not have the timeout on the connection but only on the command timeout.  

Can you please offer your ideas/suggestions to help me understand how to get back on track today?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
can you show the code that you are using to get this error?
0
Jimbo99999Author Commented:
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
0
Jimbo99999Author Commented:
I can run it from the project to try and find the issue.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Éric MoreauSenior .Net ConsultantCommented:
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.
0
Jimbo99999Author Commented:
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()
0
Éric MoreauSenior .Net ConsultantCommented:
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

Open in new window

0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
>>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
0
Jimbo99999Author Commented:
I took off all of the connection timeouts and set all the command timeouts to 0 and one of them still failed!!!
0
Éric MoreauSenior .Net ConsultantCommented:
-place a try...catch around each command execution so that way you will be able to find which one is causing the problem
0
Jimbo99999Author Commented:
- 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
0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
you have an issue with your query then. Something in your query is trying to add a null value.
0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
Have you been able to find an exception?
0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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?
0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
could you try something to simplify the query? I would split in 2 queries:

UPDATE [Processing Table] SET
[Last Updated] = getDate()
WHERE SCAC ='23250'
and [Audit Reason 4]  = '" & CarrierInvoiceNumber & "'"


UPDATE [Processing Table] SET
[Last Updated] = getDate()
WHERE SCAC <> '23250'
and [Premium Invoice Number] = '" & CarrierInvoiceNumber & "'"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jimbo99999Author Commented:
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.
0
Jimbo99999Author Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
I wouldn't set the TimeOut to at all (keeping the default value)
0
Jimbo99999Author Commented:
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
0
Jimbo99999Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.