Connectivity fails while executing queries against SQL through VBA code

  • Article

Connectivity fails while executing queries against SQL through VBA code

*************************************************************

When we execute queries against the SQL through VBA code generates the below error - and fails to complete the code execution. This issue occurs only while executing against the SBS Server:

"[Microsoft][SQL Native Client]TCP Provider: A connection attempt failed because the remote host did not properly respond after a period   of time, or the establish connection failed because the connected host failed to respond."

Steps towards the Solution

===================

1. While executing the VBA code on Microsoft Excel, which executes SQL Commands and insert the data with the data in the Excel sheet.

2. It may work fine in Windows 2003 Server - SQL Express, but it will fail on SBS.

3. While trying to run the code - it throws the below error message:

"[Microsoft][SQL Native Client]TCP Provider: A connection attempt failed because the remote host did not properly respond after a period  of time, or the establish connection failed because the connected host failed to respond."

4. We will not be able to trace the issue with Profiler, as SQL Workgroup Edition doesn’t come up with Profiler.

5. Examining the VBA Code - revealed that the way the application was coded was forcing ADO to open additional implicit connections against SQL every time they ran one of the stored procedure

6. Those connections are not eligible for connection pooling and therefore it either ended up exhausting the maximum number of sockets we could open from the client, or got blocked by the existing limitation in the SBS server, which was hosting the instance of SQL Server. More information about the implicit connections in this article https://support.microsoft.com/kb/271128/en-us.

Now, the question raises on how it was working with Windows 2003 & Windows XP.

Finally this question paved the way in identifying the root cause of the issue.

- ISA server is installed with SBS.

- Found that there is a policy on maximum number of incoming connection was set in ISA server is - 160 Connections.

- Analyzing the network trace was showing connections to a maximum count of 158 - so, it proved that VBA code is failing to create more connections beyond the specified limit imposed by the Restriction on ISA.

Resolution:

Removed the policy - which imposed maximum number of connections on ISA.

Venkatraghavan N

SE, Microsoft SQL Server

 

Reviewed By:

Ouseph Devis T

Tech Lead, Microsoft SQL server