Solved

Time out in a SQL Query VB.NET

Posted on 2011-09-17
8
312 Views
Last Modified: 2012-05-12
Hi all,

Im getting the following message when updating records on a SQL Server:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The code is very simple:

Dim cadena2 As String = "update available_mills set status=1 where id=99 and location=@f3"
Dim cmd2 As New SqlCommand(cadena2, con2)
cmd2.CommandType = CommandType.Text
cmd2.Parameters.AddWithValue("@f3", F3)
cmd2.ExecuteNonQuery()

This updates about 4000 records every hour

Thanks!
0
Comment
Question by:pvg1975
8 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36554544
set connection timeout to max

con2.timeout=0 this will be no time out
0
 

Author Comment

by:pvg1975
ID: 36554559
Im getting

Error      1      'timeout' is not a member of 'System.Data.SqlClient.SqlConnection'.

When using con2.timeout=0
0
 

Author Comment

by:pvg1975
ID: 36554589
I used this:

con2.CreateCommand.CommandTimeout = 0

And it seems to work. Am I doing it right? or createcommand is for something else?

Thanks!
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 15

Accepted Solution

by:
Haris Djulic earned 250 total points
ID: 36554755
it is beter to use the timeout with the command since:

SqlCommand.CommandTimeout = timeout limit for your SQL query. Means, how much time a (eg: SELECT, UPDATE) query can take for its execution. If it exceeds SqlCommand.CommandTimeout, then it stops execution. A command timeout error will occur.

SqlConnection.ConnectionTimeout = timeout limit for your connection. Means, how much time your connection object can try to connect. If it exceeds the specified time, it stops connecting. A connection timeout error will occur.

you can use it like that

cmd2.CommandTimeout =0
0
 

Author Comment

by:pvg1975
ID: 36554888
Thanks, i tried it but i get an error. I will paste the error once im back home. Thanks.
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36555928
A sqlConnection has a ConnectionTimeout property. It  is a property which determines the maximum number of seconds your code will wait for a connection to the database to open. It is set in the connectionstring to the database

data source=BROCHIS;initial catalog=Indato;integrated security=SSPI;Connect Timeout=60;

Opening the connection does not do anything in the database yet. That is handled by a SqlCommand, By default this is 30 seconds. When you perform something costly in the database this is the one whose value you’ll have to change.

SqlCommand cmd;

cmd.CommandTimeout = 60;

Hope this will work...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 36556778
There is simply no reason to increase the CommandTimeout (let alone set it to 0-Infinte) for such a simple UPDATE.  All you need to do is optimize your query.

Do you have an index on id, location?
0
 

Author Closing Comment

by:pvg1975
ID: 36556815
That worked perfect. I also optimized the table and added an index to the ID field. Thanks guys!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question