Solved

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

Posted on 2004-04-20
16
12,381 Views
Last Modified: 2012-06-27
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
Comment
Question by:Sandeater79
16 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10873511
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
 
LVL 11

Expert Comment

by:Sven
ID: 10875675
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
 

Author Comment

by:Sandeater79
ID: 10880798
Arbert:

Will try that. Thanx.


DarthSonic:

We're connecting to the server using TCP/IP and using IP address.  
0
 

Author Comment

by:Sandeater79
ID: 10881133
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
 
LVL 34

Expert Comment

by:arbert
ID: 10881417
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
 

Author Comment

by:Sandeater79
ID: 10881465
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
 
LVL 34

Expert Comment

by:arbert
ID: 10881939
I don't imagine the development server has the load that the production server has right?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Sandeater79
ID: 10882735
arbert:

Correct. The development server has much less load.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10883036
Like I said above, I would suspect some diskio is possibly causing this--run perfmon and take a look....
0
 

Author Comment

by:Sandeater79
ID: 10883144
Where can I get Perfmon? And how do I run it?  I'm not very experienced with SQL Server at all.
0
 

Author Comment

by:Sandeater79
ID: 10892276
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
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 10895071
"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
 

Author Comment

by:Sandeater79
ID: 10903239
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
 
LVL 34

Expert Comment

by:arbert
ID: 10903286
Not really sure--post  a "question" in the CS area with a link to this question and ask them to reduce the points.

0
 

Expert Comment

by:virajw2310
ID: 23711509
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now