ODBC Failure on TCP/IP

[Guest_imported]

We have made some simple changes to our LAN, added internet on an NAT router. since then a custom program that we developed in Access with SQL Server table loses it's connectivity when updating a recordset of 165 records. it used to stop at 43 records now it goes to 57. I have tried replacing hubs for switch, removing unessary protocols from the network but nothing seem to make a significant difference except to have increased the number of records that get through to 57 from 43. I can update the table manually through cut and paste but not through the program on a remote host. the program does work on the server itself.

Any help would be greatly appriciated.

 

[mvdmeij]

Could you give some more info:

What kind of db-connection is used (e.g. OLEDB, ODBC, ADO..)?
What the update looks like in the access application?
How long it takes for the update to complete?

I can't imagine that such a change to the network, will have an impact on the application. Unless Internet traffic closes out all other commuication, but then youalso should have problems with other apps.





Good luck,

Mark.

 

[fractalschaos]

the records are configured as DAO recordsets. Essientially the program goes through each record in a temp(local table) and equates fields of the temp table to the fields in the SQL table and performs a .update command when it has processed all 165 records. it takes about 1 minute or so. now since the changes to the network it loses the ODBC connection after 57 records.

it has been suggested that i remove the static ip's of the network and let the NAT Router handle all of that through DHCP. The router has a 4 port switch connected to it and so it has also been suggested that i alter the network topology and connect the SQL server to the router rather than a local switch connected to the router. It is also possible that there may be 2 subnets on the network but i won't know that until i return tomorrow.

I agree that the network changes should not have caused this problem but it is also clear that this is a network problem because it works fine on the local server.

I am grateful for any insight you can provide.

thank you

Rodney

 

[MikeTom]

look at your sql error logs. any network or ods errors if so try nt error logs see if their is corresponding error. it may be the connection. if not the connection try determine how long it takes i would usin profiler to see what is going on. as test maybe try remove indexes an do the update see if it gets further.

 

[fractalschaos]

well i have tried to change the network topology as outlined above. the network is now configured to run DHCP and the Router table is appropriate or giving proper reading. However the problem still persists.

MikeTom, I have reviewed the error logs but they give me no information on the server side. Should i review the logs on the client side? I also tried to view the database log but it seems to be an LDF file for which i am not familiar with. I don't know how to access it.

It seems that some of the utilities that come with SQL may be missing. i am new to SQL Server so i am not sure. I am running SQL 7 and the NT logs tells me that SQL Server Agent had trouble loading. when i try to load it manually i get an error that this application is intented for version 6.5 or better. Why is that? should SQL Sever Agent be running? I am loosing a lot of time with this problem but i am also learning a lot. Any help is greatly appreciated.

Rodney

 

[mungdrool]

When you say you lose your odbc connection, what do you mean exactly? Do you get an error message, or do the records just quit coming and your application eventually time out? I am having a similar problem connecting through a VPN from a web server using ADO. The query seems to only return one random packet of data, but the query has completed on the database server. Eventually the requesting machine asks for another packet, but gets no response from the database server. The same query works fine if not going through a VPN. Is this similar to your problem? I'm still working on this one.

Thanks,
MungDrool

 

[fractalschaos]

MungDrool

Your problem sounds similar but it is not exactly the same problem i think. i loose the ODBC connection at record 57 when trying to do a batch update of 165 records from a client computer.

I am however, interested in getting this software going through a VPN because one of the salemen is supposed to be getting a laptop and wants access to enter his orders. don't currently know how to do that.

 

[mungdrool]

Thanks for the info. If I find out what is causing our problem I will let you know. I have a feeling it's a firewall configuration issue, but I'm not a network guy, so I don't have access to everything I need to be sure. We did run a sniffer on our side to see that we were receiving one packet of data (not the first or the last packet, but one in the middle). We haven't sniffed the database server's firewall yet to see what it is sending.

Thanks,
MungDrool

 

[pmdawg]

I am getting the same issue on our end our Workstations are connected through a DHCP, and the IP's are Dynamic the ODBC cuts off once every 2 or 3 days. It gives a Communication Link Failure error. If anyone knows how to resolve this issue please keep me informed.

Thanks,


Paul