Maximum user connections?

[Maddman]

Newbie here.

I've got my database set up in SQL 2000, and have started an Access adp
for a front end. I have 10 licenses, and at the moment the only
accesses are the server through Remote Desktop and one person into the
ADP. I started getting ODBC timeout errors when running a complex
query, so I started poking around. In the logs there's messages saying
the maximum of 10 user connections has been reached, over and over
again. I'm not even certain that these problems are related, but it
doesn't look good.

Why are all 10 connections used when there's only the server and one
client? No one else has access to this server. And how can I stop the
ODBC timeout? That paticular complex query is the whole jsutification
for using SQL over Access, so I kind of need it to work. :)
Thanks in advance, maddman

[Simon Hayes]

"Maddman" <ma********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Newbie here.

I've got my database set up in SQL 2000, and have started an Access adp
for a front end. I have 10 licenses, and at the moment the only
accesses are the server through Remote Desktop and one person into the
ADP. I started getting ODBC timeout errors when running a complex
query, so I started poking around. In the logs there's messages saying
the maximum of 10 user connections has been reached, over and over
again. I'm not even certain that these problems are related, but it
doesn't look good.

Why are all 10 connections used when there's only the server and one
client? No one else has access to this server. And how can I stop the
ODBC timeout? That paticular complex query is the whole jsutification
for using SQL over Access, so I kind of need it to work. :)
Thanks in advance, maddman

I don't know much about ADPs or ODBC, but you can use sp_who2 to view the
current connections (from Query Analyzer):

exec sp_who2

Note that if you use Query Analyzer for your SQL coding, you may have
multiple connections open - plus another one for the Object Browser - so a
couple of active developers could easily use up the ten connections.

As for the timeout, it may be legitimate if the query runs for more than 30
seconds (which I think is the default for ODBC) - have you run it in Query
Analyzer to see how long it takes? You might also want to check if any
processes are blocking each other (the BlkBy column in the sp_who2 output),
as one connection could time out waiting for another to free a lock.

Simon

[Erland Sommarskog]

Maddman (ma********@yahoo.com) writes:

I've got my database set up in SQL 2000, and have started an Access adp
for a front end. I have 10 licenses, and at the moment the only
accesses are the server through Remote Desktop and one person into the
ADP. I started getting ODBC timeout errors when running a complex
query, so I started poking around. In the logs there's messages saying
the maximum of 10 user connections has been reached, over and over
again. I'm not even certain that these problems are related, but it
doesn't look good.

Why are all 10 connections used when there's only the server and one
client? No one else has access to this server. And how can I stop the
ODBC timeout? That paticular complex query is the whole jsutification
for using SQL over Access, so I kind of need it to work. :)
Thanks in advance, maddman

As Simon said, the 10 connections may all come from the same machine.
You can easily have more 10 connections from Access to SQL Server if
you are not careful. sp_who2 as Simon mention should give the origin
of the connections.

It is not likely that the number of connections have anything to do with
the timeout, though.

You say you are using ODBC, but what precisely does this mean? If you
are using ADO, I would suggest that you rip out Driver={SQL Server} from
the connection string and replace it with Provider=SQLOLEDB. Not
that it will resolve the timeout problem, as the 30 seconds default
timeout will be there anyway.

If you are using ADO, set the .CommandTimeout on the Command and Connection
objects to 0, and there will be no more timeouts.

Of course, the query may need tuning as well, for instance adding better
indexes, but that's another story.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Maddman]

Ah, when I said 'query' I should have said 'View'. Can you tell I'm
just coming from Access? :)

Anyway, when I try to run the view by right-clicking and selecting
Return all rows I get the odbc timeout error. This makes no sense to
me, as the tables its looking at are all inside the database, none of
them are going over odbc. I get the same error if I try to view it
from Access. I increased by # of connections to 20, but that didn't
seem to help anything.

Just to be certain, I copied the SELECT statement from the View and ran
it in the Query Analyzer. It ran just fine, took about four minutes to
calculate everything. But I can't get the data as a view for some
reason.

[Andy O'Neill]

"Maddman" <ma********@yahoo.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

Ah, when I said 'query' I should have said 'View'. Can you tell I'm
just coming from Access? :)

Anyway, when I try to run the view by right-clicking and selecting
Return all rows I get the odbc timeout error. This makes no sense to
me, as the tables its looking at are all inside the database, none of
them are going over odbc. I get the same error if I try to view it
from Access. I increased by # of connections to 20, but that didn't
seem to help anything.

Just to be certain, I copied the SELECT statement from the View and ran
it in the Query Analyzer. It ran just fine, took about four minutes to
calculate everything. But I can't get the data as a view for some
reason.

4 minutes doesn't strike me as being an acceptable response time.
Perhaps you want to think about de-normalising and doing the calculations
you intimate in triggers....
Or some such.

As people have mentioned, you can clock up a number of connections from the
one access adp.
Perhaps the techniques used or number of licences need to be reconsidered as
well.
More licenses might be cheaper than a complete rewrite.

--
Regards,
Andy O'Neill

[Erland Sommarskog]

Maddman (ma********@yahoo.com) writes:

Anyway, when I try to run the view by right-clicking and selecting
Return all rows I get the odbc timeout error. This makes no sense to
me, as the tables its looking at are all inside the database, none of
them are going over odbc. I get the same error if I try to view it
from Access. I increased by # of connections to 20, but that didn't
seem to help anything.
I don't know what you mean here when you say that nothing goes over ODBC.
If that means that you are querying table in Access, you need to go to
an Access forum. If the tables really are in SQL Server, yes, then you
are going over ODBC (or OLE DB, depending on which client library you
are using). SQL Server is, just that, a server, and all communication
are through some sort of network connection.
Just to be certain, I copied the SELECT statement from the View and ran
it in the Query Analyzer. It ran just fine, took about four minutes to
calculate everything. But I can't get the data as a view for some
reason.

If it takes four minutes to run the query from Query Analyzer, and
the default timeout in Access is 30 seconds, guess what happens when
you submit the query from Access?

It appears that you need to increase the timeout. The number of
connections has not anything to do with it.

Now, how you increase the timeout in Access, is definitely a question for
an Access newsgroup. I left a hint in my previous post, but it appears
that you have some view in Access, and it should be clear by now, that
my knowledge of Access is non-existent.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Maddman]

I understand the ODBC timeout when going through access. But when I go
to the View I have set up with this SELECT query, I get the same ODBC
timeout manager. Does it use ODBC to communicate within the components
of the Enterprise Manager?

[Maddman]

I took a look at the structure of the queries. What I've got here is
several views that add up bits of data per ID Code, then a final view
that summarized all of them together. I moved around when some of the
calculations are made and that took care of the problem. Went from 4
minutes to about 10 seconds.

Thanks guys!

[Erland Sommarskog]

Maddman (ma********@yahoo.com) writes:

I understand the ODBC timeout when going through access. But when I go
to the View I have set up with this SELECT query, I get the same ODBC
timeout manager. Does it use ODBC to communicate within the components
of the Enterprise Manager?

Eh? I'm sorry you have almost lost me completely.

But if you talk about retrieving the result from view in Enterprise
Manager, yes, you could get an ODBC timeout. Enterprise Manager is a
client, just like Access.

You could get an ODBC timeout in Query Analyzer as well. The difference
is that ODBC has a default timeout of 0, but you can configure one if
you like.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp