SQL Server does not exist or access denied

[kwdavids]

I'm getting occasional instances of the error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied

I get the error both in application code and from Enterprise Manager.
The database is LOCAL.

Microsoft SQL Server 2000 Developer Edition SP4
Windows XP Professional SP2

My application essentially reads one row from one table, reads some
additional rows from the another table in the same local database, and
then writes one row into another table in another local database (and
commits the transaction). The application process approximately 3932
input records, then the read for the additional rows fails with the
error above. With identical input data sometimes it will fail after
3931 rows, sometimes 3932, sometimes 3933. Once it fails, it will fail
for the next 5-6 input rows, and then start working for a few thousand
rows more, then error again.

Note that the application should not actually connect to the database
when I get this error, it's just opening a dataset on an already-open
connection.

During the time the application is getting errors (about 3 minutes
duration at a stretch), attempting to access the same local server with
Enterprise Manager gives the same "SQL Server does not exist or access
denied" error.

While things are failing, I observe that SQL Server processes are not
using any CPU (in task manager).

Things I have tried:
Disabling Norton Antivirus and Internet Worm Detection
Configuring SQL Server to use only one processor (it's a 2-Xeon
processor machine)

The only thing that seems to work is to set the connection timeout and
command timeout to 6 minutes.

[Hugo Kornelis]

Re: SQL Server does not exist or access denied

On 25 Jan 2006 07:59:45 -0800, kwdavids wrote:
[color=blue]
>I'm getting occasional instances of the error:
>
>[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>access denied[/color]
(snip)[color=blue]
>The only thing that seems to work is to set the connection timeout and
>command timeout to 6 minutes.[/color]

Hi kwdavids,

And I assume that this "fix" replaces your problem with the problem of
intermittend long waits?

It sounds as if a periodic process is blocking your reads, causing them
to wait until the process is finished. To identify this process:

1. Increase the timeout, as you've already tried;
2. Monitor the application until the delays start;
3. Use sp_lock and sp_who2 in a Query Analyzer window to identify the
process that is blocking your reads.

--
Hugo Kornelis, SQL Server MVP

[kwdavids]

Re: SQL Server does not exist or access denied

Thanks Hugo,

Yes the "fix" does result in intermittent long waits.

While the application was "waiting" I ran sp_lock and sp_who2 and I
didn't see that anything was waiting for a lock. I also tried to
connect to the database from Enterprise Manager and got the Error
message (it times out pretty quick).

[Hugo Kornelis]

Re: SQL Server does not exist or access denied

On 27 Jan 2006 07:56:46 -0800, kwdavids wrote:
[color=blue]
>Thanks Hugo,
>
>Yes the "fix" does result in intermittent long waits.
>
>While the application was "waiting" I ran sp_lock and sp_who2 and I
>didn't see that anything was waiting for a lock. I also tried to
>connect to the database from Enterprise Manager and got the Error
>message (it times out pretty quick).[/color]

Hi kwdavids,

In addition to Erland's suggestions - set up a profiler trace, leave it
running until you have experienced one of those 6-minute delays, then
check for irregularities. (For instance, if no events were captured at
all during that time, the problem is in the connectivity.)

--
Hugo Kornelis, SQL Server MVP

[kwdavids]

Re: SQL Server does not exist or access denied

For what it's worth...

It turns out that I had set up two "firehose" DataSets on the same
connection. This resulted in the 2nd connection being opened and closed
thousands of times by ADO. In my situation, SQL Server is going out to
lunch after about 6,000 connections.

Putting the second query on another connection has stopped the multiple
connections and SQL server locks up no more.

[Hugo Kornelis]

Re: SQL Server does not exist or access denied

On 30 Jan 2006 10:26:25 -0800, kwdavids wrote:
[color=blue]
>For what it's worth...
>
>It turns out that I had set up two "firehose" DataSets on the same
>connection. This resulted in the 2nd connection being opened and closed
>thousands of times by ADO. In my situation, SQL Server is going out to
>lunch after about 6,000 connections.
>
>Putting the second query on another connection has stopped the multiple
>connections and SQL server locks up no more.[/color]

Hi kwdavids,

Thanks for reporting back the cause of the delays. And I'm glad to hear
that you've got this figured out!

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Re: SQL Server does not exist or access denied

kwdavids (kevin@davnet.o rg) writes:[color=blue]
> For what it's worth...
>
> It turns out that I had set up two "firehose" DataSets on the same
> connection. This resulted in the 2nd connection being opened and closed
> thousands of times by ADO. In my situation, SQL Server is going out to
> lunch after about 6,000 connections.[/color]

Or rather shared memory. TCP/IP connections would probably still work
if TCP/IP is enabled, as will existing connection. This was one of the
scenarios I suggested, and I been able to produce this situation myself
when running a tight loop with connection/disconnection without pooling.
(To check for memory leaks.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx