Solved

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

Posted on 2004-04-20
16
12,805 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
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!

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

679 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