• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1975
  • Last Modified:

VFP connection to SQL server intermitent disconnect

We have a network with 40 users.   Up to 30 might use the network at the same time.

About a month ago we changed our ISP provider.   Since then we have been experiencing intermittent communication errors to all 3 servers.   There are about 15 error messages a day.  Most of them are to the SQL server, but we also have a few  to the LAN server and  to the Exchange server.
We think the communication failures are not related to the ISP provider change, since the change had not included hardware or software updates.  However, we might be missing something about that.
Since many of the error messages mention Microsoft ODBC SQL server Driver, we removed some old unused FoxPro ODBC drivers on workstations that were used to generate Crystal reports using FoxPro files.    It seems that removing the FoxPro ODBCs from work stations might be helping  the situation temporarily.   That also might be a coincident.  We have been using the same ODBCs for about 10 years without any problems for generating Crystal reports from our Visual FoxPro accounting system without causing any problems.  It is hard to judge what helps since the issues are intermittent.

Some potential clues:   Many times the communication errors take place when the work station had been inactive for a while.  
Some times during heavy usage the whole system slows down and several people get communication failures around  the same time.
Some times after experiencing a loss of  communication to the SQL server, we notice that the workstation shows a message Connection to the Microsoft Exchange Server has been restored.

Most of the error messages are the following:
Error # [ 1526]: Connectivity error: [Microsoft][ODBC SQL Server Driver] Communication link failure SQL command &.    
Error # [ 1526]: Connectivity error: [Microsoft][ODBC SQL Server Driver] [DENTLIB]ConnectionWrite (send()). SQL command &&
The SQL command that fails varies, but these are all commands that have been working properly for years.


Error reading file &   
The file being read varies, but these are all major files that all other users are able to read.  These are errors related to files on our LAN server.


We have a Windows LAN server with Windows Server 2003, a SQL 2005 server (on another machine with Windows Server  2007 copyright) and an Exchange server.
0
AmotzBoneh
Asked:
AmotzBoneh
  • 5
  • 5
1 Solution
 
TeethingCommented:
If you changed ISPs, did you also change routers and/or firewalls?


Do you have dhcp on your internal network from one of your windows machines?  


If so, check to ensure DHCP is disabled on the new equipment.If you don't change anything internally, changing ISPs shouldn't effect your internal machines if they are just accessing local hardware.

0
 
AmotzBonehAuthor Commented:
Very interesting.  We are checking this theory.   What kind of issues would the network experience if DHCP was turned on on the new router and on a server on our network as well.
0
 
TeethingCommented:
If this is the case you will get sporadic connectivity issues like this.  As your dhcp leases expire, they will send out requests broadcast throughout the entire network (unless you have routers breaking up the broadcast domain) and the first device that answers is the one that assigns the ip.

When you get these issues, can you ping your servers from the command prompt ?  
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
AmotzBonehAuthor Commented:
Hello Teething,  Here's what our network person said:  I checked the firewall settings... There was one anomoly related to DHCP... Although the internal dhcp scope defined on the firewall was completely disabled, the overall DHCP engine (which can be used on any firewall zone) was still running.  Normally that does not matter, however, I felt it would be a good idea to turn it off.  Let me know if this makes any difference at all.  The moment you get an error (if you get one), we can eliminate DHCP as a potential cause and move on to the next test that I have in mind (you won't like it).
After he made the change we had one new error.    
0
 
TeethingCommented:
Are all of your servers internal ? Are these cloned machines?  

On the workstations that are having this issue, you may want to disable the power management features of the nic.  Right click my computer, select manage, Click on Device Manager, then find the Network Adapters, expand the list, right click on your adapter, select properties, click on the Power Management tab, and uncheck the " Allow the computer to turn this device off to conserve power" box, hit apply, then ok.

You can also try updating the drivers of the nic.


0
 
AmotzBonehAuthor Commented:
Hello Teething,  Thank you for your assistance.   We have one large server which has several virtual servers in it.   The machines are HP cloned.   We did disable the power management on the machines.   Last thing we tried was to upgrade the switches.   I changed the application to refresh the link to the SQL server if it is not working.   This helped a lot from users perspective, but we are still not sure why there is a disconnect.  Also, sometimes users still get error messages if the system is looking for a LAN file after the disconnect and the applicable LAN file had not been stored to CASH memory.
0
 
TeethingCommented:
Does your large server supporting the VMs do anything else but support VMs ?  

Do you have a dedicated NIC for VMs ?  

If that NIC is dedicated for VMs to share, ensure that it takes precedence in your network bindings.  In server2008, got to the control panel, then network connections, then click on the Advanced tool, then click Advanced Settings.  In your adapter bindings tab, the VM NIC should be first, then the virtual adapter you create should be next.

This will give precedence to network traffic incoming to your servers.
0
 
TeethingCommented:
Does your large server supporting the VMs do anything else but support VMs ?  

Do you have a dedicated NIC for VMs ?  

If that NIC is dedicated for VMs to share, ensure that it takes precedence in your network bindings.  In server2008, got to the control panel, then network connections, then click on the Advanced tool, then click Advanced Settings.  In your adapter bindings tab, the VM NIC should be first, then the virtual adapter you create should be next.

This will give precedence to network traffic incoming to your servers.
0
 
AmotzBonehAuthor Commented:
Seems that we stopped having the problem once we updated our switches.   The points Teething made were all excellent and therefore I am accepting the solution.    Last few times I accepted the solution I was kicked out and had to log in again and start all over.
0
 
AmotzBonehAuthor Commented:
Seems that we stopped having the problem once we updated our switches.   The points Teething made were all excellent and therefore I am accepting the solution.    Last few times I accepted the solution I was kicked out and had to log in again and start all over.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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