?
Solved

timeout error for update statement

Posted on 2011-04-29
8
Medium Priority
?
325 Views
Last Modified: 2012-05-11
The earlier solution works perfectly in updating the table but when I run it for over 10,000 lines it stops after doing a few hundred updates.  I recieve the following error:  "The timeout period elapsed prior to completion of the operation or the server is not responding."  

Is this due to the opening of connections over and over?  Is there any way to fix it?  Is there a more efficient way to do this?  If not I can do the update in smaller batches.  I was just wondering if I was doing something wrong.
0
Comment
Question by:InfoTeam
8 Comments
 
LVL 9

Expert Comment

by:radcaesar
ID: 35493427
How did you execute ur update?

.NET? If yes try below solutions. Before that, I suspect some performance in your code/server. Check that.

1. Connection Timeout for Connection. It could be solved by setting ConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can set CommandTimeout property to Command object. I recommend you set CommandTimeOut property to bigger one value.
0
 

Author Comment

by:InfoTeam
ID: 35493428
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35493434
Before changing the defualt CommandTimeout consider adding an index on gpid.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 35493451
And even before that, I would stronglly urge you to change that whole RBAR (Row By Agonizing Row) mess.
0
 

Author Comment

by:InfoTeam
ID: 35494512
Perk,
How else can you do it?  I think I only know the unfortunate RBAR mess way.  I'm sure its something with server...maybe.  Thanks for looking at it anyway.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 400 total points
ID: 35496196
Please increase the timeout period in Connectionstring.
0
 
LVL 8

Accepted Solution

by:
Ghunaima earned 800 total points
ID: 35496346
Another way is to save all the values returned by the getallusers to a table first then use a single update command for updation.

Also in current logic use only the connection 'conn', there is no need to define "conn1". Also dont forget to close the connection at the end of the procedure.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 800 total points
ID: 35502176
How many rows are there in tblAssets?  Do you have an index on gpid?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

850 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