0

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)

2
  • I'd recommend running the SELECT top 1000 ClientID FROM Clients query directly from SSMS on the machine in the satellite office (in KY, USA) just to take MS Access out of the equation. You may even get a more meaningful error in that case. Or if it works, then I'd circle back on what's specific to MS Access that would cause it to fail.
    – J.D.
    Jul 14, 2022 at 12:54
  • 1
    @J.D. I did also create a windows script to rule out MS Access. That also fails/works in the same conditions (but with a less useful error message). I will give SSMS a try (will need to install it)
    – MrVimes
    Jul 14, 2022 at 13:01

1 Answer 1

0

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=****

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.