Timeout Error accessing SQL through ADO

[StormbringerX]

SQL Server 2005, Windows XP

A little background: My company does NOT have the server maintenance contract for this particular client. We do however provide all client software. Recently, the server was upgraded to Win2003 server and the SQL was upgraded from SQL Server 2000 to SQL Server 2005.

We access our SQL tables using ADO so we did not anticipate any problems with this upgrade, and there hasn't been any until now.

Basically, we allow the user to choose a Beginning and Ending Item number, we populate a listview based on their criteria, and once they press the Begin command button, we go down through the listview and for each listitem we then populate a recordset with all of that item's children. At that point, we Move First/Next through the recordset and for each child item found, we execute a stored procedure that recalculates it (using ADO Command). Once the recordset hits EOF, we close the recordset, and get the next parent in the listview.

This has worked well in the past. Its not as fast as dumping everything to the server and executing a stored procedure, but the user likes seeing little check marks appear on each listview item checkbox as it recalculates. *shrug*

But now, after the upgrade, it will execute approximately 4000 times, and then it will hit an SQL timeout error.

I realize that this isn't the quickest possible way to perform this action, however basically we are opening a connection, executing a small stored procedure, closing the connection, retrieving the next record, etc...

I have changed the adodb command.timeout property to no effect. Tried the same procedure from several clients and the behavior is consistent. Made certain that the tables have indexes and that the indexes are correct. I really don't know what else to do or check. Any suggestions would be greatly appreciated.
Thanks,
Dave

 

[macleod1021]

The first thought that came to my mind was the connection string...but then you said it works through about 4000 records. The only other thing that I can think of at the moment is your time outs. What do you have the timeout set to on your ADO as well as your Remote query timeout within the SQL Server (server properties->Connections). The default is 600 seconds...does that sound about how long it's lasting????

 

[danvlas]

You could try to kill the connection and re-instate it every time you execute the re-calculation stored proc?
May turn even slower than now, but you won't have the timeout problem anymore...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

There is a CommandTimeOut on the Conncection as well, did you set it.

 

[StormbringerX]

Heres an update of sorts. I'm now receiving a different message: [DBNETLIB][ConnectionOpen(PreLoginHandshake()]General Network Error

In response to this, I have placed the servername and IP in the host file, downloaded and installed the newest version of MDAC, but it made no difference.

I also looked at the DNS Server event viewer and it's loaded up with warnings. The time stamps indicate that during the night a warning is generated every 1 hour and 3 minutes. DUring the day (working hours) it generates one every 3 minutes.

Same message: The DNS server has encountered numerous run-time events. Blah blah blah, with a link to the Microsoft Knowledge base that pulls up Microsoft's ohsohelpful We're sorry, there is no additional information about this issue....

As I said, my company does not have the maintenance on the server itself, so I plan to turn this over to someone else. It appears to me to be an issue with the DNS, rather than a software/ADO/SQL problem.

Thank you for all of your input and if I'm told what the problem actually is, I will certainly let you know.

 

[gmmastros]

If it's a DNS error, then I would suggest that you connect to the server's IP Address instead of the server name.

For example, a typical connection string looks like this...

Provider=SQLNCLI;Server=[!]ServerName[/!];Database=DataBaseName;Trusted_Connection=yes;

What I am suggesting is....

Provider=SQLNCLI;Server=[!]192.168.0.101[/!];Database=[!]DataBaseName[/!];Trusted_Connection=yes;

Use the IP Address instead. Sometimes it is necessary to specify the port that SQL Server is using. The example I show below assumes the default SQL Server port, it would be best to determine the actual port number instead. If you need help with this, let me know and I will show you how to determine it. If this applies to you, then the connection string would look like this...

Provider=SQLNCLI;Server=192.168.0.101[!],1433[/!];Database=DataBaseName;Trusted_Connection=yes;

You add the port number after the IP Address seperated by a comma.

Obviously, I don't know if this will solve your problem, but it is the next step that I would take to troubleshoot this issue.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StormbringerX]

Hi George,

I did as you suggested (excellent suggestion by the way. My mind wasn't even going down that path) and it still stopped with an error after approximately 4000 records, so it must not have anything to do with DNS.

The error was different however: SQL Server does not exist or access denied.

Its almost as if after a certain amount of traffic it just refuses to do any more accessing. I had at first thought it was a specific record that was causing the problem, but I can use that same record that it stopped on as a starting point and it continues on for approx 4000 records past it.

I cant help but believe that there is some setting on the SQL Server 2005 that Im not aware of. Everything was fine on SQL 2000... I'm at a loss...

 

[gmmastros]

I had a similar problem with one of my customers. Currently, I have over 100 customers with approximately 5 users per customer. That's about 400-500 computers running my application. I had this problem with just one of them. Unfortunately, the way I resolved it was to do everything in 1 big stored procedure.

In my case, the process (when looping and calling an individual stored procedure) took approximately 2 minutes to run. Since I was looping in VB, I was able to show a nice little progress bar. To resolve the problem, I created 1 SP that handles all the items. They no longer get a progress bar, but the process takes less than 1/2 the time too.

I know this is not helpful for you. I guess the only reason I posted this is because I can sympathize with your situation.

There are probably some workarounds that you could implement. For example, instead of processing each record at a time, maybe you could batch the records (ex: process 100 records at a time). Then, you could 'show the checkboxes' to the user as you go along, but still make the whole process faster (by batching 100 records at a time) and more stable (by circumventing this problem).

If there really is a setting that would prevent this, I'd be interested to know what it is too.

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StormbringerX]

I appreciate the sympathy... it is a vexing problem. My first instinct is to blame the SQL 2005 upgrade. But at the same time, they did the Windows 2003 Server upgrade AND added some new switches within the network, so the possibilities are multiple. And since my co doesn't maintain the hardware, network or server, my hands are tied as to how far I can go in researching the problem. Additionally, all the users know is that our software is "giving them errors". So.... rock and a hard place. I may have to do the same as you simply to keep the customer happy.

Thanks again George, hope you have a great Christmas!