Error: Database server connection limit exceeded

[Ralph]

I am receiving this error in an application which utilises
ADO.NET to access an embedded SQl Anywhere version 11
server. I am closing each connection after each call to the
database however I am still receiving the error.

How do I avoid this error or is work around this error?

[Jeff Albion [Sybase iAnywhere]]

Raplh,

ADO.NET uses connection pooling by default. (See:
http://dcx.sybase.com/1100en/dbprogramming_en11/connection-pooling-dotnet-dev.html
).

(e.g. By default with pooling enabled, .Close() returns the connection
to the connection pool and doesn't actually close the database
connection.
http://dcx.sybase.com/1100en/dbprogramming_en11/ianywhere-data-sqlanywhere-saconnection-close2-0.html
)

The default connection pool size is 100, so it's possible you're
exceeding your license limits.

1. Turn off connection pooling (POOLING=FALSE in the connection string)
2. Use the "Max Pool Size=50;Min Pool Size=5" connection options to
ensure you're working within your license limits
3. Increase your licensed seats.

Regards,

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0

[Ralph]

I understand that connection pooling is on by default and
the connection string that I'm using is the same for all
calls to the database so connections should be reused.
However, it does not appear as though they are. I am using
the personal server and hence there are only 10 connections
allowed. But each call to the database appears to create a
new connection.

I am closing each SAConnection object after the call to the
database, however, I am actually closing DbConnection as
opposed to SAConnection. I have a base class defined with
DbConnection, DbCommand etc., which I am inheriting in
conjunction with some factory methods to provide the actual
SAConnection and SACommand objects.

Does SAConnection.Close() do stuff other than make a call to
base.Close(). If so, I would be losing the additional
functionality in my implementation?

Regards, Ralph

[Jeff Albion [Sybase iAnywhere]]

Ralph,

Ralph wrote:
> I understand that connection pooling is on by default and
> the connection string that I'm using is the same for all
> calls to the database so connections should be reused.
> However, it does not appear as though they are. I am using
> the personal server and hence there are only 10 connections
> allowed. But each call to the database appears to create a
> new connection.

I'd have to see an example to explain this precisely, but I don't think
we guarantee anything about the condition of the connection that we pull
from the pool (if it's been used before or not). If you're using the
personal server, I'd strongly suggest shrinking your pool to a maximum
of 10 connections since using anything larger will never be able to be
allocated anyway.

> I am closing each SAConnection object after the call to the
> database, however, I am actually closing DbConnection as
> opposed to SAConnection. I have a base class defined with
> DbConnection, DbCommand etc., which I am inheriting in
> conjunction with some factory methods to provide the actual
> SAConnection and SACommand objects.
>
> Does SAConnection.Close() do stuff other than make a call to
> base.Close(). If so, I would be losing the additional
> functionality in my implementation?

We don't actually call base.Close() - our Close() is defined as an
override function from DbConnection. So, I'm assuming that anything
you're doing in the base class upon .Close() is not getting called.