SQL Server does not exist or access denied

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.

http://support.microsoft.com/defaul...kb;en-us;328306

Nothing in KB 328306 applies to my situation.

kwdavids (kevin@davnet.org)  writes:
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r
> 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.

Or at least so you like to think.

What I have seen is that there are some ways to get shared memory to become
sour. And shared memory is usually what you use for connection on a local
server.

One way is to open QA, stop SQL Server and restart. You can now notice
that opening new windows or starting OSQL takes a long time.

The other way I have encountered this was when I ran a script that for
test purposes connected and disconnected in a tight loop - and I had
turned off conncetion pooling for the test.

Connection Pooling is usually on in ADO .Net, but if you are not handling
your connections correctly, then the pool will dry out. But that usually
leads to an error message from ADO .Net before you wreck shared memory.

You would try opening the SQL Server Client Utility and an uncheck
shared memory, to see what effect this gives.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks Erland,

Disabling shared memory had no effect.

An argument against the memory corruption theory is that the problem
straightens itself out after 30 seconds to a couple of minutes.

This is really a very simple application (only 3 datasets total), and
nothing else of any significance is running at the same time.

It's as if SQL Server just stops responding from time to time (both to
Enterprise Manager and my application). I can't find any locks, nor is
the workstation using any CPU cycles.

kwdavids (kevin@davnet.org)  writes:
> An argument against the memory corruption theory is that the problem
> straightens itself out after 30 seconds to a couple of minutes.
>
> This is really a very simple application (only 3 datasets total), and
> nothing else of any significance is running at the same time.
>
> It's as if SQL Server just stops responding from time to time (both to
> Enterprise Manager and my application). I can't find any locks, nor is
> the workstation using any CPU cycles.

Autogrow is a faint possibility, but an autogrow that takes that long
would extend the database with a couple of GB, and it does sound like
we are talking that size of the database.

One place to check is the SQL Server error log, to see if there are
any messages. Also look at the starting time of the log, so that it's
not the case that SQL Server stops and restarts.

Also check for things outside SQL Server. Do you run any disk defragmenter
or anti-virus?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Not auto-grow (too small), nothing in the SQL Server logs. No de-frag.
I run Norton Anti-Virus, but disabling it had no effect.

What I did find through server tracing, is that for some reason (which
I haven't been able to discover yet), each time the secondary DataSet
is being opened, there is another connection to the database. So the
problem might be "every few thousand connections". I'm building a test
bed application to try to simulate what's going on in the larger
program.