All we need is an easy explanation of the problem, so here it is.
I recently swapped database servers (restored databases from old to new and then swapped IPs)…
Old: Sql Server 2005, Windows Server 2003
New: Sql Server 2008, Windows Server 2012
All processes that connect to the server (dot net website, PHP, vbscript, mssql) from a geographically nearby VPN work fine on the new server.
But we have a VPN between the server (UK) and a satelite office (KY USA) and a database application running on Microsoft Access, and this application is throwing an error when performing queries on the new server. The error is….
Error: ODBC–call failed.[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
(#10054) [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network
error. Check your network documentation. (#11)
If I temporarily have this application query the old server (by changing the local hosts file) the error doesn’t occur and everything works.
To help with troubleshooting this I built a small script…
'ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _ ' "OLE DB Services=-2;Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*" ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _ "Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*" set conn = CreateObject("ADODB.Connection") Conn.connectiontimeout=500 Conn.commandtimeout=500 conn.open ConString SCRIPTTIMEOUT = 1000 'sqlstring="SELECT top 100 ClientID FROM Clients" sqlstring="SELECT top 1000 ClientID FROM Clients" 'sqlstring="SELECT ClientID FROM Clients" 'succeeds with top 100 'fails with top 1000 or no limit set Rcount = Conn.Execute(sqlstring) msgbox Rcount(0) Rcount.Close set Rcount = Nothing set sqlstring = Nothing Conn.Close set Conn = Nothing
The script works if I query for 100 rows. If I try 1000 it fails every time (I haven’t yey tried intermediate amounts but I don’t see much point narrowing it down)
The script works if I switch the IP address to the old server (Everything else is the same – same vpn, same client, same script)
The script and the access application work fine in every way over a different VPN (Also UK)
- I have trawled through every setting I can find on each server (in MS SQL Studio, Configuration Manager, Network Settings) to try to match, and try different settings.
- I have stopped and started services
- I have googled this and tried every suggestion I could find (I’ve lost track, but I have tried adding
OLE DB Services=-2. I have tried
Encrypt=on(and off). I have tried
Trusted_connection=on(and off). I have tried
Pooling=on(and off). I have tried specifying the instance name after the IP.
Both servers are in the same physical rack, on the same network/subnet, connected to the same physical switch.
Before I go down the route of messing with VPN settings, swapping wires (Requiring access to the DC) is there something I can/should do/look at on the new server to help?
Edit: If I add
Packet Size=1024; to my connection string it works! Thing is, both servers have that set to 4096 by default, and the old server works just fine with that.
Edit 2 The above worked for my test script, but not for the access app.
I am hoping to find a workaround that doesn’t require a connection string change because that is going to be a hassle (involving the developer of the access app)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Not my ideal solution (as I was hoping to avoid changing connection strings) but I solved this in my test script by adding
Packet Size=1024 into the connection string.
This didn’t work for the Access app. However I then created a little test access app, and by doing so I was able to figure out that the change wasn’t working in the main Access app because the connection string was using a different driver and fields. By using the same connection string in the Access app that I was using in my test script it worked.
So it seems the
Packet Size=1024 setting either isn’t recognised or is ignored in the connection string of the following format…
DRIVER=SQL Server;SERVER=live-db-server,1433;Packet Size=1024;UID=xxx;PWD=xxx; APP=Microsoft Office 2016;DATABASE=Perf
But does work in the alternate connection string…
Provider=SQLOLEDB;Data Source=live-db-server,1433;Packet Size=1024; Initial Catalog=Perf;Network=DBMSSOCN;User Id=****;Password=****
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂