Cannot allocate more connections....

[Geir]

Sybase Version: 12.5.2
Microsoft .NET Framework Version:1.1.4322.573
ASP.NET Version:1.1.4322.573

Then I try to run a simple query from an ASP.NET
application. the web-client got this error message: Cannot
allocate more connections. Connection pool is at maximum.
Increase Max Pool Size. This error message will show after
eight run of the same query.

My Connection String: Provider=Sybase ASE OLE DB
Provider;Data
Source='SYBASE';Port='5003';UID='ASPNET';PWD='<passwd>';Database='product';
Connection Timeout = 60;

My .NET code:
Public Function GetPortalsAliasByTabId(ByVal TabId As
Integer) As String
Dim myConnection As AseConnection = New
AseConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim strSQL As String = " SELECT
W_Portals.PortalAlias FROM W_Tabs, W_Portals"
strSQL += " WHERE W_Tabs.PortalID =
W_Portals.PortalID"
strSQL += " AND W_Tabs.TabID = @TabId "
Dim myCommand As AseCommand = New
AseCommand(strSQL, myConnection)

Dim parameterTabId As AseParameter = New
AseParameter("@TabId", AseDbType.Integer, 4)
parameterTabId.Value = TabId
myCommand.Parameters.Add(parameterTabId)

' Open the database connection and execute the
command
myConnection.Open()
Dim dr As AseDataReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Return the datareader
Return dr
End Function

[Geir]

Note:
Public Func...) As AseDataReader NOT As String

[Paul]

Hi

Sybase ASE OLE DB Provider is not supported in the .NET enironment, per the
vendor. However the pooling as far as I know is handled by .NET but may
involve instructions from the data provider to increase the MAX pool size.
Unfortunately the ASE OLE DB Provider has no such facility, so I would not
know how to increase this.

One confusing thing is that your connection string indicates use of the
System.Data.OleDb namespace, since you are indicating this in the connection
string. However I do not know all the code so I am not sure.

Next - your ASP.NET code implies that you are using the Sybase ASE ADO.NET
Data Provider, based on the object names, so I am confused about the
connection string.

With the ADO.NET Provider you need to get ESD#03 (Sybase SDK 12.5.1). In
the doc there are descriptions for Connection pool properties: max pool size
and min pool size, defaults of 100 and 20 respectively. If you are only
gettting 8 you might be using the earlier ADO.NET provider. In ebf 11786 a
problem was fixed:

343967 ADO.NET: The "Max Pool Size" connection property is not being
utilized to limit the maximum number of concurrent connections to the
database.

So you might be hitting this IF you are using the Sybase ASE ADO.NET Data
Provider. If using Sybase ASE OLE DB Provider, I am not sure how to set
limits on the pooling since that product has no such capabilities that I
know of.

Paul

<Geir> wrote in message news:40dc0881.5de...@sybase.com...

[Paul]

Hi,

I just wrote up a reply but do not see it posted:

Your code is confusing - you have connection object indicating use of Sybase
ASE OLE DB Provider, which is not supported in the .NET environment, per the
vendor who makes the product for Sybase. You .NET code looks like it is
using code written for the Sybase ASE ADO.NET Data Provider, which is a
native ADO.NET Provider.

There are no properties available on the OLE DB Provider to manage pooling,
and I am not sure if the MS system handles this itself - so I do not know
why you ar elimited.

On the Sybase ASE ADO.NET Provider there was a problem in the origina l
release resolved in ESD #3, EBF 11786:

343967 ADO.NET: The "Max Pool Size" connection property is not being
utilized to limit the maximum number of concurrent connections to the
database.

The native provider has 2 properties available to manage pool size - Max
Pool Size and Min Pool Size, default values are 100 and 20 respectively.

Maybe this is why you have the problem - is it possible you posted a
different connection string?

Paul

<Geir> wrote in message news:40dc0881.5de...@sybase.com...

[Geir]

I can' see any changes in my connection if I use the
Provider key or not. If I remove the Provider key setting,
the connection still working. If I use Provider='Hi.Geir',
still working...

My current connection string in web.config:
<add key="ConnectionString" value="Provider=Sybase ASE .NET
Data Provider;Data
Source='NOMOLDWEB04';Port='5003';UID='<uid>';PWD='<pwd>';Database='product';
Connection Timeout='60';" />

- We use Sybase Adapive Server Enterprise Small Buisness
Edition 12.5.2. There are noe EBF available on this version.
Can I use EBF 11786 on the 12.5.2 version?
- Sybase.Data.AseClient.dll version 1.1.327.0.
- Sybase ASE .NET Data Provider - sybdrvado11.dll version
1.1.327.0
- Sybase Ase Data Provider SSL API - sybdrvssl.dll version
12.5.1.327

You refer to Max/Min Pool Size. I can't find any
configurational settings in the Server enviroment to set
this. Can I use some setting in my connection string or
vb.net code?

:->Geir

[Geir]

A shot from the hip. I'm trying 'Pooling=False' in my
connection string. Application working fine...

:->Geir

> I can' see any changes in my connection if I use the
> Provider key or not. If I remove the Provider key setting,
> the connection still working. If I use Provider='Hi.Geir',
> still working...
>
> My current connection string in web.config:
> <add key="ConnectionString" value="Provider=Sybase ASE

> NET Data Provider;Data

[Paul]

OK - that will turn off the pooling completely.

The ASE (Server) has nothing to do with the pool settings - this is all
controled by the Provider and the applicaiton. The Min/Max settings I refer
to are for the Connection String.

I am figuring you code is importing the Sybase.Data.AseClient assembly
somehow - if using .NET this is the requirement to use the provider. If
this is the case then the "Provider" attribute in your connection string is
being completely ignored, since the Sybase ASE ADO.NET Provider is loaded in
memory and connection occurs because you include Data Source, which
corelates to the hostname of ASE, and Port. Therefore it would not matter
what the Provider attribute was set to.

So, if you want to engage pooling, use the Min/Max settings as indicated.
EBF 11910 is the latest EBF and you should be able to use this. I am not
sure how you originally installed the ASE ADO.NET Provider, but the EBF
mentioned has the latest build and Yes it will work with ASE 12.5.2.

Paul

One very peculiar thing is how your connection string is working with the
reset of the application.
<Geir> wrote in message news:40dfbe6d.10a...@sybase.com...

[Geir]

Thanx Paul...I will try to install the EBF 11910 and
implement connection pool settings in my application. I'm
not sure about the gain...

Assembly Impl. in vb code:
Imports Sybase.Data.AseClient
Namespace glxPortal
Public Function GetSomething...
....
End Function

Public Function AddSomething...
....
End Function

End Namespace

Note: I very impressed by the speed of our application after
changes from OLEDB to ADO.NET. In some cases we increased
speed by 700% ....belive it...!

:->Geir

[Paul]

Hi Geir,

I am glad to hear about the boost in performance. Pooling will help when
you have many concurrent connections. However, there is not too much
overhead in the connection phase. Still, when applying to some web
application or similar situation the pooling will probably help out.

Paul

<Geir> wrote in message news:40e158e3.2f4...@sybase.com...

[Raidu]

I recently hit by this. I found that there are database connection leaks
from my application. To check if there are any connection leaks, turn off
the connection pooling and make sure that you do not see any open processes
from your application in the Sybase Process monitor.

Then this issue was resolved.

Thanks!
Paramesh

"Paul" <Pa...@sybase.com> wrote in message news:40e18eee$1@forums-1-dub...