Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13915
  • Last Modified:

Getting [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()) errors

Hi All,

We're getting the following error whenever we run our billing system:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv())

or sometimes:

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv())

It is caused by calling the execute method of an ADOConnection object instantiated in Delphi 6. The code looks like this:

              with ADOConnection do
                Execute('Update Billing_AccountServices '+
                  'set BillingRun_ID = '+IntToStr(iBillingRun_ID)+
                  ' where Account_ID = '+IntToStr(LastSubAccount_ID)+
                  '   and ServiceID = '+IntToStr(FieldValues['ServiceID']));


This error occurs after several thousand transactions have been processed using the same ADOConnection.  From what I can see, the function call times out even if I set the CommandTimeout property to 120 seconds.  However, if there aren't many transactions processed (I can't say the exact figure), function call doesn't time out and the error doesn't occur. Everything seems to be fine.

The weird thing is that we get this error most of the time we run the billing system.  However, once in a while (I'd say 1 in 30 runs), we can run the billing system and none of these errors occur.  It runs fine even though we didn't change anything in the code, in SQL Server or in ODBC settings.

System Info
--------------
- database is SQL Server 2000 SP3
- MDAC 2.7 on both client machine and server
- running Delphi 6 application that uses ADO to connect and retrieve data from database
- OS is WinXP on client machine and Win2K on server
- connection is by ODBC using TCP/IP and IP address

Basically, I'd like to have someone explain to me what the problem is and either point me in the right direction or tell me how to fix it.

Thank you all for your responses.
0
Sandeater79
Asked:
Sandeater79
1 Solution
 
arbertCommented:
We have been noticing this quite a bit lately on one of our systems.  Our culprit appears to be IO waits on the database and the client, for some reason, seems to ignore the timeout.

So, I would look at the server and see if you're having any locking/blocking and also fire up Perfmon and see if you see any disk queues....
0
 
SvenTech Lead Web-DevelopmentCommented:
We had have the same problems (and still have them sometimes). We could minimize this erros by installing MDAC 2.8 and deisabling SQL Servers Named Pipes (allways use TCP/IP, connect through servers IP not name).

This did not prevent these erros but minimize them on our server.
0
 
Sandeater79Author Commented:
Arbert:

Will try that. Thanx.


DarthSonic:

We're connecting to the server using TCP/IP and using IP address.  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Sandeater79Author Commented:
Btw,

I forgot to mention that we've run this on a development server (on-site) that has the exact same configurations and it runs fine. Only when I run it in our production server (off-site) does it give us the errors.  

The development server is a Win2K3 Server box running SQL Server 2000.  We connect to it through TCP/IP but using name of server.
0
 
arbertCommented:
I've found that "exact same configurations" is hardly ever true.  Whether it's different amounts of data, out-dated statistics, different indexes, different drive configurations....
0
 
Sandeater79Author Commented:
arbert:

Yes, I realize that's true, let's say similar:)  Hmm, both databases are from the same backup and nothing was changed so both should have the same indexes.  Driver configurations are definitely different but I'm not sure the details on that.  MDAC on development server is 2.8. Um...can't think of any other configuration details..

0
 
arbertCommented:
I don't imagine the development server has the load that the production server has right?
0
 
Sandeater79Author Commented:
arbert:

Correct. The development server has much less load.
0
 
arbertCommented:
Like I said above, I would suspect some diskio is possibly causing this--run perfmon and take a look....
0
 
Sandeater79Author Commented:
Where can I get Perfmon? And how do I run it?  I'm not very experienced with SQL Server at all.
0
 
Sandeater79Author Commented:
I did an ODBC trace and I found these errors:

DIAG [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). (10054)

DIAG [08S01] [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation. (11)

This looks like a network problem to me. Would I be correct in assuming this?
0
 
arbertCommented:
"This looks like a network problem to me. Would I be correct in assuming this?"

Nope, a general network error simply means that SQL was disconnecte for some reason....

Perfmon is the built in windows profiling tool.  Start/Run/Perfmon.EXE
0
 
Sandeater79Author Commented:
It seems I've found the cause and solution to my problem.  I'm using ZoneAlarm Pro as my client firewall and when I set Internet Zone Security to High, I get the errors. When I lower the setting to Medium, then I don't get the error.  

I think this has solved the issue for now.  Anybody have any thoughts on this?

arbert:  How do I give partial points? I'd like to give you 125 points since you've been answering my questions.
0
 
arbertCommented:
Not really sure--post  a "question" in the CS area with a link to this question and ask them to reduce the points.

0
 
virajw2310Commented:
We also getting same error, ODBC Connection timeout. In fact when my clients trying to view PowerPlay reports also they get disconnected from the server connection. Please refer attached screen shot. Further same connection time out is common while clients get connected to SQL 200 Server.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now