Re: error -2147467259 sql server does not exist or access is denied

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Frank Lehmann (f.leh294_at_chat4world.com)
Date: 02/25/05 

Date: Fri, 25 Feb 2005 09:03:52 +0100

Hi Eli,

The connection may have got lost because the client machine opens
a new TCP port with every access to the SQL Server without
re-using the connections/ports previously opened. (TCP port is not
equivalent to SQL Server connection.)

You can watch this behaviour with the netstat service program run in a
DOS window. Connections not being re-used get into the TIME_WAIT state
and will be automatically deleted/released after 4 minutes, unless the TCP
settings are re-configured.

According to how frequently an application accesses the server, it might
happen that you get thousands of TIME_WAIT connections in a relatively short
period of time, until the resources are dead.

This error occurs sporadically, depending on how much TCP ports
are open/released again on the client machine at a given moment.
It results in errors like
[DBNETLIB]SQL server does not exist or access denied
[DBNETLIB]General network error, and a few others.

To mitigate this, you could introduce a second ADO.Connection in
critical places, so that connection pooling works or rather works at all.
Critical place is, for instance, if you open a recordset and before closing
it, you open a second recordset via the same ADO.Connection. Take a
different one. (This is my standard reply, and it may not apply to your
specific application.)
By this measure, I was able to reduce the number of open TCP ports
from 1000 to 3 in my application.

Other measures are Registry TCP settings (That may apply to your app):
MaxUserPort:
Increase the number of TCP ports to allow more open ports.

TcpTimedWaitDelay:
Decrease the number of seconds, until a TCP port is released (down to 30).

in
HKLM\System\ControlSet\Services\Tcpip\Parameters

-------
Frank

Relevant Pages