connection pooling problem w/ ASP.NET 2.0 and SQL 2005 64 bit?

Here's the gist of the problem I am having:
I just deployed my new ASP.NET 2.0 / C# web app to "production". I have a
web server running Windows 2003, all the latest updates, etc. As mentioned,
the web app is running under ASP.NET 2 and C3. On a separate server, I am
running SQL Server 2005 64 bit, Standard Edition (SP1).
Note, I have separate development and test environments that mimic the above
setup. That is I have a dev web server accessing a separate dev database
server; the only difference is that the db server is SQL 2005 32 bit, Std
Edition; same in test.
My production web app is experiencing connection pooling problems. If I
bring up the activity monitor on the production db server while I am
exercising some of the web pages, I can watch the connections grow to the
100 max (the default for asp.net) very quickly (just one person hitting a
page that exercises a database query) and then the .NET app will eventually
throw an error:
Message: Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached.
Source: System.Data...
Some things to consider:
1) Before everyone jumps to the conclusion about datareaders, please bear in
mind that the EXACT same .NET code is on dev and test web servers; I am NOT
able to get the connections to max out on dev or test; whereas on production
web server, I can easily get it to max out. It's not a code issue. Yes, I
know all about closing DataReader connections explicitly, blah, blah. It's
not the code.
2) There are 2 major difference on the db server; however
a) the production server is 64 bit; my other db servers are 32 bit
b) I actually spent quite a bit of time today on my production db server in
an effort to get linked server stuff working. So, there are some definite
things I did on the prod server db that I have *not* done on dev or test sql
boxes, such as
- turning on MS DTC security config settings (in Component Services); there
are many MS blogs about this and the following MSKB:
http://support.microsoft.com/kb/899191
- enabling named pipes
So, I'm thinking I have 2 straws to grasp at:
A) call Microsoft tech support to see if there's anything that google hasn't
turned up
B) uninstall SQL Server 2005 on my production db server and reinstall using
a 32 bit version. Of course, since this is production. I bought 64 bit SQL,
so would I be "allowed" to downgrade to 32 bit? The only additional disc
media I have are MSDN discs.Hi,
Thanks for using Microsoft Managed Newsgroup.
From your description, I understand that:
Your ASP.NET 2.0 web application with SQL Server 2005 64 bit SP1 was
running on a production environment. The database server was on a separated
machine. You found that the connections grew quickly to reach the max
connection limit on your SQL Server and then any new connecion was timeout.
However no problem appeared on your test and development environment.
If I have misunderstood, please let me know.
For further research, I would like your answering me several questions:
1. How long had the SQL Server been normally running before the problem
appeared?
2. What are the situations of CPU and memory usage at that time?
3. Could you see some errors in the Event logs?
Also, I would like to collect the following information from you:
1. MPSReport:
Please help create a MPSReport on the problematic machine, which will
collect
ERRORLOG, Windows event log and other helpful information about the SQL
Server. To
create a MPSReport, please visit the following web site:
<http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F
-88B7-F9
C79B7306C0&displaylang=en>.
And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
the
prompted dialog boxes and the readme window (usually a Notepad) after you
read
them. After the Command Prompt window closes itself, collect the cab file
the tool
has generated and sent it to me.
2. SQL Error log:
By default, they are located at: C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG -- Send all the files under this folder with zip
format to me.
3. Server Configurations:
Use sqlcmd or osql to run the following batch and send the output file to
me:
Under command prompt: sqlcmd -S ServerName\InstanceName -E -o C:\output.txt
exec sp_configure 'show advanced', 1
reconfigure with override
exec sp_configure
go
4. SQL Trace file
Use SQL Profiler to monitor your SQL Server to see what are the SQLs or SPs
being executed at that time.
I will create a web file space for your uploading the large files. However
you may need to leave me your email so that I can mail you the password.
If it is not convenient for you to leave your email here, you can send an
email to me (changliw@.microsoft.com).
Note: Our managed newsgroup is focused on break/fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if you need further assistance on this issue.
We appreciate your posting back at your convenience and let us know the
issue status.
If you have any other questions or concerns, please feel free to let us
know. It is always our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

Connection pooling problem

Can anybody help me to solve this problem

I have a web application. In this app i have one class genral for database transections. I create new object of this class on every page in page load event & dispose it on UnLoad event. After sometime this app gives connection pooling exception.

Means connection are not closed, but i disposed the object like

tmpDatabase.Dispose()
tmpDatabase = Nothing

Plz can anybody tell me the mistake i m doing.

ThanxPersonally, I would contruct the object as late as possible (where needed) and destroy the object as soon as possible, and let connection pooling work as designed.

Perhaps there is som error condition that you are not handling, so that the dispose is sometimes not reached.|||I can see using Break point, it destroying the object. Means cursor reaching to the line where the object is destroyed.|||Please Help me

This is big problem for my application. I dont know what i doing wrong.|||"This is big problem for my application. I dont know what i doing wrong."

We do not either. In general, there is some reason why your connections are not being closed. Could be an error condition not properly handled, could be a design problem. Absent seeing much of the code, and absent you taking my advice to make the Open and Close very close to each other, leaving the connection open for as short a time as possible rather than creating one for a page load/unload cycle, I do not think anyone can help you.

Connection Pooling on Analysis Services

Please may I have your assistance with the following issue. Let me 1st describe the scenario. The front end uses BEA Web Logic which queries an IIS Web Service that in turns queries MS Analysis Services 2005.

The Web Service builds the MDX queries dynamically and returns the results in XML.

Each time we run a query we open a connection using ADOMD, fire the MDX to return the result set then close the connection. However we don't think this will provide the best performance. So

1. Does the AS OLEDB provider handle connection pooling internally?

2. If not, is it possible to implement connection pooling in code inside our web service?

3. Are there other ways to implement connection pooling? for example does using HTTP connections automatically provide us with connection pooling?

Thanks

Here is an article about implementing connection pooling in AS2000, the same should apply for AS2005.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_connpooling.asp

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Many thanks for the document. Please have a look at the following.

http://www.codecomments.com/archive364-2005-1-379959.html

What would be ideal is when the web service starts this will automatically create a fixed number of connection pools to an AS Db

Our objective is to be able to reuse these connection pools by different users connecting to the same web service, obviously not simultaneously. The above suggests that the connection pool is destroyed?

Any thoughts would be greatly appreciated

Thanks

John

|||

No.

The post you mention suggests only that you should apply a bit more logic beyond simple 3 step;

1. Application starts.
2. It creates pool of open connecitions size N.
3. Pool of connections is destroyed when you shut down your web service.

It suggests you implement logic in your application that allows your application to grow conneciton pool above pre-set limit N:
In beginning your conneciton pool is N , if application used all of the connections from the conneciton pool and still needs more connections, connection pool will grow to N +x ... Your application will periodically check if it needs to srink connection pool back to N.

As for the different users connecting to your application. You can re-use connections if application is using same credentials to retrevie data from Analysis Server.
For instance if user A and B connecting to your web service running under W credentials. If your connections are opened under W , you can re-use them . If you open connections under A , you should not let B re-use that conneciton , otherwize you might show B too much data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward, Many thanks for your tips and based on your recommendation (to pursue AS 2000) downloaded the MS XML FOR AS SDK (AS 2000) and started to look at the various options

We used the reference to this

Public Shared p As New MSXmlAnalysisSCLib.ADOConPool

p.MaxSessions = 5

cnadomd = p.GetConnection("Data Source=myASServer" & ";Provider=msolap.3; initial catalog=myASDb")

Unfortunately we dropped the above method because there are no options to use XML? This will mean we would need to read the cells and construct our own XML string

Then we looked at the option of using http connection using the latest AS 2005 connection provider which seems to be set up to use connection pooling

conn.ConnectionString = "Data Source=http://myWebServer/OLAPhttp/msmdpump.dll" & ";Provider=msolap.3; initial catalog=myASDb"

As the content of the msmdpump.ini file suggests ::

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>10</ConnectionPoolSize>

<MinThreadPoolSize>1</MinThreadPoolSize>

<MaxThreadPoolSize>10</MaxThreadPoolSize>

<MaxThreadsPerClient>5</MaxThreadsPerClient>

</ConfigurationSettings>

However from perfmon as well as SQL profiler, when issuing multiple connections simultaneously using the above http method it will use the same connection Id, but create multiple ‘current user sessions’. Also the observed execution time was rather slow.

The alternative is to use the following

conn.ConnectionString = "Data Source=myASServer;Provider=msolap.3; initial catalog=myASDb"

which is observed to work correctly when we use multiple connection simultaneously and performs well.

However the issue I have with the above is where do I instantiate the above so that I can re-use the connection. I noticed in IIS we can configure application pooling.

A low level example would be greatly appreciated, if you don’t mind?

Thanks

John

|||

First you can try and increase the ConnectionPoolSize in the msmdpump.ini .

Creating a single connection and having it be reused across several sessions should have comparable or better performance to opening new TCP connections all the time.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.
Gai
Visit on this web site
http://www.able-consulting.com/ADO_C...erForSQLServer -- CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.
As far as I know, if you create a connection to the sql server and then
impersonate a new user and make a new connection, a new connection pool gets
created even if the connection string is the same.
Hopes this helps
Marc
"Gai" <gaitamir@.IBezint> wrote in message
news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>
|||Yes, but again, I'm impersonating a single user (not the running user, but
DomainA\UsernameA).
Hope it's more clear now.
Thanks any way, Gai.
"Marc Mertens" <marc.mertens@.azlan.com> wrote in message
news:eGpFc6s0EHA.1260@.TK2MSFTNGP12.phx.gbl...
> As far as I know, if you create a connection to the sql server and then
> impersonate a new user and make a new connection, a new connection pool
gets[vbcol=seagreen]
> created even if the connection string is the same.
> Hopes this helps
> Marc
> "Gai" <gaitamir@.IBezint> wrote in message
> news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
not.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.Gai
Visit on this web site
http://www.able-consulting.com/ADO_...derForSQLServer --
CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.Gai
Visit on this web site
http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForSQLServer -- CONNECTIONS
"Gai" <gaitamir@.IBezint> wrote in message
news:%23we0Fah0EHA.1396@.tk2msftngp13.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>

Connection pooling & Windows authentication

Hey all,
I'm connecting to SqlServer 2000 from an ASP.NET web application.
The web application runs under a specific Identity (impersonating to a
specific windows account), the connection string contains
"IntegratedSecurity=SSPI",
and as far as I can see, it (the connection string) is constant.
Will my web application be using the connection pooling mechanism? I'm
asking this because I've been reading that if the conenction string
is constant, the connection pool will be used, however, I haven't found
anything about windows authentication, where the username and password
aren't specified in the connection string, but are managed by windows,
therefore I can't really say if the connection srting is different or not.
Thanks, Gai.As far as I know, if you create a connection to the sql server and then
impersonate a new user and make a new connection, a new connection pool gets
created even if the connection string is the same.
Hopes this helps
Marc
"Gai" <gaitamir@.IBezint> wrote in message
news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hey all,
> I'm connecting to SqlServer 2000 from an ASP.NET web application.
> The web application runs under a specific Identity (impersonating to a
> specific windows account), the connection string contains
> "IntegratedSecurity=SSPI",
> and as far as I can see, it (the connection string) is constant.
> Will my web application be using the connection pooling mechanism? I'm
> asking this because I've been reading that if the conenction string
> is constant, the connection pool will be used, however, I haven't found
> anything about windows authentication, where the username and password
> aren't specified in the connection string, but are managed by windows,
> therefore I can't really say if the connection srting is different or not.
> Thanks, Gai.
>|||Yes, but again, I'm impersonating a single user (not the running user, but
DomainA\UsernameA).
Hope it's more clear now.
Thanks any way, Gai.
"Marc Mertens" <marc.mertens@.azlan.com> wrote in message
news:eGpFc6s0EHA.1260@.TK2MSFTNGP12.phx.gbl...
> As far as I know, if you create a connection to the sql server and then
> impersonate a new user and make a new connection, a new connection pool
gets
> created even if the connection string is the same.
> Hopes this helps
> Marc
> "Gai" <gaitamir@.IBezint> wrote in message
> news:#otPhYh0EHA.2568@.TK2MSFTNGP10.phx.gbl...
not.[vbcol=seagreen]
>sqlsql

Connection Pooling

I am currently building an OLAP application in AS2005 which is going to
integrate with an existing web application that uses Connection Pooling.
This will mean that there is only a few distinct accounts which access the
AS database (although there will be thousands of users), and I would like to
utilise the new CustomData connection string parameter to feed account ID's
through to AS2005 as a source for security.
Has anyone out there used AS2005 with connection pooling in this way?, and
secondly has anyone used the new CustomData parameter, and can this be
altered in the connection string without forcing a new connection to be
generated; which would obviously negate the reason for Connection Pooling...
Thanks.From my experience, if you alter the connection string, it creates a NEW
connection, and also creates a new Connection Pool for that new connection.
If your users are domain authenticated users, then even if they are using a
common connection account, their domain identity can still be known by SQL
Server.
You may want to read up on SESSION_USER() in books on Line.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>I am currently building an OLAP application in AS2005 which is going to
> integrate with an existing web application that uses Connection Pooling.
> This will mean that there is only a few distinct accounts which access the
> AS database (although there will be thousands of users), and I would like
> to
> utilise the new CustomData connection string parameter to feed account
> ID's
> through to AS2005 as a source for security.
> Has anyone out there used AS2005 with connection pooling in this way?, and
> secondly has anyone used the new CustomData parameter, and can this be
> altered in the connection string without forcing a new connection to be
> generated; which would obviously negate the reason for Connection
> Pooling...
> Thanks.
>|||Thanks for this Arnie.
The issue I have is that I want to feed in a user ID from the web
application session, which will not be the ID used to access AS2005, but is
needed to base the security. The users domain login is unfortuantely no use
here as the IDs are completely seperate.
Is it feasible to think that I could set a system parameter (which wouldn't
effect other sessions) for the current session, and then access this
parameter from within AS2005, or perhaps by executing a stored procedure from
within AS?
"Arnie Rowland" wrote:
> From my experience, if you alter the connection string, it creates a NEW
> connection, and also creates a new Connection Pool for that new connection.
> If your users are domain authenticated users, then even if they are using a
> common connection account, their domain identity can still be known by SQL
> Server.
> You may want to read up on SESSION_USER() in books on Line.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "SimonE" <SimonE@.discussions.microsoft.com> wrote in message
> news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
> >I am currently building an OLAP application in AS2005 which is going to
> > integrate with an existing web application that uses Connection Pooling.
> >
> > This will mean that there is only a few distinct accounts which access the
> > AS database (although there will be thousands of users), and I would like
> > to
> > utilise the new CustomData connection string parameter to feed account
> > ID's
> > through to AS2005 as a source for security.
> >
> > Has anyone out there used AS2005 with connection pooling in this way?, and
> > secondly has anyone used the new CustomData parameter, and can this be
> > altered in the connection string without forcing a new connection to be
> > generated; which would obviously negate the reason for Connection
> > Pooling...
> >
> > Thanks.
> >
>
>|||You could just add the UsedID as an INPUT parameter to the stored procedures
that are called to provide the users the information they seek. Of course,
if your users are running dynamic queries from QA or a similar tool, that
wouldn't work. If you have a base of stored procedures in place and don't
want to add the UserID input param, you could create a wrapper stored
procedure with the UserID input param that then verifies (or logs, etc.)
user information and then executes the current stored procedures.
As far as I know, there is no 'simple' way to pass in the UserID identity as
an element of the connection string. Using a common connection string will,
as you know, have the effect that every request from the web server will be
seen as coming from the same users.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:E591F675-72DC-4DC8-98CB-596C3EEC8E49@.microsoft.com...
> Thanks for this Arnie.
> The issue I have is that I want to feed in a user ID from the web
> application session, which will not be the ID used to access AS2005, but
> is
> needed to base the security. The users domain login is unfortuantely no
> use
> here as the IDs are completely seperate.
> Is it feasible to think that I could set a system parameter (which
> wouldn't
> effect other sessions) for the current session, and then access this
> parameter from within AS2005, or perhaps by executing a stored procedure
> from
> within AS?
>
> "Arnie Rowland" wrote:
>> From my experience, if you alter the connection string, it creates a NEW
>> connection, and also creates a new Connection Pool for that new
>> connection.
>> If your users are domain authenticated users, then even if they are using
>> a
>> common connection account, their domain identity can still be known by
>> SQL
>> Server.
>> You may want to read up on SESSION_USER() in books on Line.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "SimonE" <SimonE@.discussions.microsoft.com> wrote in message
>> news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>> >I am currently building an OLAP application in AS2005 which is going to
>> > integrate with an existing web application that uses Connection
>> > Pooling.
>> >
>> > This will mean that there is only a few distinct accounts which access
>> > the
>> > AS database (although there will be thousands of users), and I would
>> > like
>> > to
>> > utilise the new CustomData connection string parameter to feed account
>> > ID's
>> > through to AS2005 as a source for security.
>> >
>> > Has anyone out there used AS2005 with connection pooling in this way?,
>> > and
>> > secondly has anyone used the new CustomData parameter, and can this be
>> > altered in the connection string without forcing a new connection to be
>> > generated; which would obviously negate the reason for Connection
>> > Pooling...
>> >
>> > Thanks.
>> >
>>

Connection Pooling

I am currently building an OLAP application in AS2005 which is going to
integrate with an existing web application that uses Connection Pooling.
This will mean that there is only a few distinct accounts which access the
AS database (although there will be thousands of users), and I would like to
utilise the new CustomData connection string parameter to feed account ID's
through to AS2005 as a source for security.
Has anyone out there used AS2005 with connection pooling in this way?, and
secondly has anyone used the new CustomData parameter, and can this be
altered in the connection string without forcing a new connection to be
generated; which would obviously negate the reason for Connection Pooling...
Thanks.From my experience, if you alter the connection string, it creates a NEW
connection, and also creates a new Connection Pool for that new connection.
If your users are domain authenticated users, then even if they are using a
common connection account, their domain identity can still be known by SQL
Server.
You may want to read up on SESSION_USER() in books on Line.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>I am currently building an OLAP application in AS2005 which is going to
> integrate with an existing web application that uses Connection Pooling.
> This will mean that there is only a few distinct accounts which access the
> AS database (although there will be thousands of users), and I would like
> to
> utilise the new CustomData connection string parameter to feed account
> ID's
> through to AS2005 as a source for security.
> Has anyone out there used AS2005 with connection pooling in this way?, and
> secondly has anyone used the new CustomData parameter, and can this be
> altered in the connection string without forcing a new connection to be
> generated; which would obviously negate the reason for Connection
> Pooling...
> Thanks.
>|||Thanks for this Arnie.
The issue I have is that I want to feed in a user ID from the web
application session, which will not be the ID used to access AS2005, but is
needed to base the security. The users domain login is unfortuantely no use
here as the IDs are completely seperate.
Is it feasible to think that I could set a system parameter (which wouldn't
effect other sessions) for the current session, and then access this
parameter from within AS2005, or perhaps by executing a stored procedure fro
m
within AS?
"Arnie Rowland" wrote:

> From my experience, if you alter the connection string, it creates a NEW
> connection, and also creates a new Connection Pool for that new connection
.
> If your users are domain authenticated users, then even if they are using
a
> common connection account, their domain identity can still be known by SQL
> Server.
> You may want to read up on SESSION_USER() in books on Line.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "SimonE" <SimonE@.discussions.microsoft.com> wrote in message
> news:9B9C0356-8E73-4930-A5CA-AFC3E5E25224@.microsoft.com...
>
>|||You could just add the UsedID as an INPUT parameter to the stored procedures
that are called to provide the users the information they seek. Of course,
if your users are running dynamic queries from QA or a similar tool, that
wouldn't work. If you have a base of stored procedures in place and don't
want to add the UserID input param, you could create a wrapper stored
procedure with the UserID input param that then verifies (or logs, etc.)
user information and then executes the current stored procedures.
As far as I know, there is no 'simple' way to pass in the UserID identity as
an element of the connection string. Using a common connection string will,
as you know, have the effect that every request from the web server will be
seen as coming from the same users.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"SimonE" <SimonE@.discussions.microsoft.com> wrote in message
news:E591F675-72DC-4DC8-98CB-596C3EEC8E49@.microsoft.com...[vbcol=seagreen]
> Thanks for this Arnie.
> The issue I have is that I want to feed in a user ID from the web
> application session, which will not be the ID used to access AS2005, but
> is
> needed to base the security. The users domain login is unfortuantely no
> use
> here as the IDs are completely seperate.
> Is it feasible to think that I could set a system parameter (which
> wouldn't
> effect other sessions) for the current session, and then access this
> parameter from within AS2005, or perhaps by executing a stored procedure
> from
> within AS?
>
> "Arnie Rowland" wrote:
>sqlsql

Connection pool leak in production machine (in other machine it runs well)

Hi to everyone,

I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.-

Thanks to everyone for any help.

How do you access DB? If you use some IDataReader, though they will be GC sooner or later, still remember to close them as soon as they are not used~|||

My big mistake is to misunderstood the meaning of the numberOfReclaimedConnection counter, that indicates the number of connections that are reclaimed by a garbage collector and indicate a leak. For some reason in my developement machine garbage collector does a more aggressive job in reclaiming connection while in the production server, maybe due to the large amount of ram garbage collection happens less frequently.

You're right about the datareder, one of the function in Data Access Layer forget to close a datareader in some situation, and it's called from a webModule that runs at every request for the page. The result is awful, when a lot of user access to the site the pool often become empty. I enclose the datareader in using block and all goes well again. From now on I will use perfmon.exe at every run of the site.

Alk.

Connection Pool Error

Hello,
I'm having a recurring error whenever I try to debug a
asp.net web page is retrieving data from a SQL server.
The error msg is:
"Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and
max pool size was reached."
Any ideas?There were some connection pooling problems in the earlier versions of .NET.
Have you applied the .NET service packs to your boxes?
--Brian
(Please reply to the newsgroups only.)
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c3d7ba$5694ff40$a001280a@.phx.gbl...

quote:

---

> Hello,
> I'm having a recurring error whenever I try to debug a
> asp.net web page is retrieving data from a SQL server.
> The error msg is:
> "Timeout expired. The timeout period elapsed prior to
> obtaining a connection from the pool. This may have
> occurred because all pooled connections were in use and
> max pool size was reached."
> Any ideas?
>

---

|||Brian (anonymous@.discussions.microsoft.com) writes:

quote:

---

> I'm having a recurring error whenever I try to debug a
> asp.net web page is retrieving data from a SQL server.
> The error msg is:
> "Timeout expired. The timeout period elapsed prior to
> obtaining a connection from the pool. This may have
> occurred because all pooled connections were in use and
> max pool size was reached."

---

ADO.Net is not my best game, but I know that there are pitfalls, so
if you are not careful, you will fill up your connection pool. One
way to diagnose this is to run sp_who from Query Analyzer, and see
how many connections you have open. If you think you have none,
but there are hundred you know that 1) you have filled up the pool
2) you have a leak.
Explicitly closing your connections is probably a good idea.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

connection leak

Hi, in my web application i had a connection leak now that i solve this problem, i want to check if all connections are really closed.

I make a connection to an sql database.

I try on the database the sp_who procedue but i think, this is not that i can see if i colse all, connections

is there a other way to check or see if all connections are closed.

Enclosing the connection objects inside the "Using" block will make sure that the connection object is disposed off properly preventing any connection leaks. For e.g:

using(SqlConnection cn = new SqlConnection())

{

//DAL code

}

Check this out for details:

codebetter.com/blogs/john.papa/archive/2005/04/01/60984.aspx

Hope this helps,

Vivek

Connection is busy with results for another hstmt & Win2003 SP1

I wrote a web app using an ASP front end (not .NET) connecting to a SQL
Server 2000 (no SP) back end. Both the web server and the database
server are Windows 2003 boxes. The app was running fine two weeks ago
before I went on vacation. While I was gone my net admin applied
Windows 2003 SP1 to the web server. Upon my return I was informed that
the app is no longer working, getting the following error:

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for
another hstmt

Since that is presumably the only thing that changed in my absence I
believe the SP somehow messed up the way the ODBC was working. I tried
moving the SQL Server database to a different Windows 2003 box which
also received the SP1 update and also includes SP3 for SQL Server, but
get the same error.

I am hitting the database like so:

set GetData = CreateObject("ADODB.Command")
GetData.ActiveConnection = SQLConn
GetData.CommandText = "<stored procedure call>"
Set DataRS= GetData.Execute (,,adCmdText)

I appear to get the error when I execute the second call. I am
learning ASP as I go, so maybe this is a trivial problem.Jegg (jsauri@.gmail.com) writes:
> I wrote a web app using an ASP front end (not .NET) connecting to a SQL
> Server 2000 (no SP) back end. Both the web server and the database
> server are Windows 2003 boxes. The app was running fine two weeks ago
> before I went on vacation. While I was gone my net admin applied
> Windows 2003 SP1 to the web server. Upon my return I was informed that
> the app is no longer working, getting the following error:
> [Microsoft][ODBC SQL Server Driver]Connection is busy with results for
> another hstmt
> Since that is presumably the only thing that changed in my absence I
> believe the SP somehow messed up the way the ODBC was working. I tried
> moving the SQL Server database to a different Windows 2003 box which
> also received the SP1 update and also includes SP3 for SQL Server, but
> get the same error.
> I am hitting the database like so:
> set GetData = CreateObject("ADODB.Command")
> GetData.ActiveConnection = SQLConn
> GetData.CommandText = "<stored procedure call>"
> Set DataRS= GetData.Execute (,,adCmdText)
> I appear to get the error when I execute the second call. I am
> learning ASP as I go, so maybe this is a trivial problem.

The gist of the error message is that you have a command that generated
one or more results, that you have not picked up, and you cannot submit
the next stored procedure for execution.

If that procedure generates result sets, you probably want that data.
Then again, it could be a stray debug result set that should not be
there.

Here are some general rules:
o Unless you want explicit row counts back from INSERT/UPDAET/DELETE
operations, submit a SET NOCOUNT ON when you connect. These rowcounts
are actually kind of result sets, and these need to be consumed.
SET NOCOUNT ON eliminates those.
o If you call a stored procedure that is not supposed to return data,
specify the option adExecuteNoRecords.
o When you run a procedure that can return data, be sure to get all
record sets, by looping over .NextRecordset.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the info. I did have nocount set to on. However, I did not
know about the adExecuteNoRecords option. That actually will come in
very handy.

On a whim I did manage to elimate the error by adding "DataRS = empty"
after the first stored procedure call. I did not have to do this with
any of the others presumably because I loop through the result set
until EOF (there was known to be only one possible record in the first
result set so I did not do this).

Thanks for your speedy reply!

connection from the pool

I build an asp.net 2.0 (VS 2005) web application and put it on the server. The installed SQL server is SQL server 2003. I get this error message when i keep the application running for 10 or 15 mins. "Timeout expired. The timeout period elapsed prior obtaining a connection from the pool. This may have occure because all pooled connections were in use and max pool size was reached."

I made the server timeout unlimited but the error keeps coming up after some time of openning the application. The application session time out redirects the user to the login page and doesn't throw an error like the one i mentioned above.

Any idea. Thanks in advance.

designstudio,

try increasing the number of connections in the pool. in your connection string, here is an example

conn.ConnectionString = "integratedsecurity=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max PoolSize=60;Connect Timeout=2;";

see where it says max pool size = ...have you tried that?

hope this helps -- jp

|||

Thanks jdingo,

I haven't tried that. I think it will work.

I'll let you know if it doesn't work.

|||

Thanks jdingo,

I haven't tried that. I think it will work.

I'll let you know if it doesn't work.

|||

You might also want to ensure that you really are closing all connections. The best way to ensure this is to use the 'using' statement in C#, or a try-finally combination where you explicitly close the connection in the finally block.

string connectionString = GetConnectionString(); // Gets the connection string in some way defined by youusing (SqlConnection connection =new SqlConnection(connectionString)){ connection.Open();// Do whatever needs to be doing. When the using-block is exited, the connection // objects Dispose() method will be called, ensure that the connection is returned // to the pool. Even if an exception occurs.}

Connection from ASP page to SQL Server Express

Hi!

I have two servers, the web (with IIS - win2003) and the test data
server (winXPpro with SQL Express)
I'm trying to connect to the db but with no success...
here is the connection string I'm using:
sConn = "Driver=SQLNCLI;
Server=marco-server\SQLEXPRESS;
Database=grimp;
Uid=grimpuser;
Pwd=somePassword;"

I get this:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

I've tried also with Driver={SQL Native Client} but I get the same
error.

do I have to install something on the web server?(ma.giorgi@.gmail.com) writes:

Quote:

Originally Posted by

I have two servers, the web (with IIS - win2003) and the test data
server (winXPpro with SQL Express)
I'm trying to connect to the db but with no success...
here is the connection string I'm using:
sConn = "Driver=SQLNCLI;
Server=marco-server\SQLEXPRESS;
Database=grimp;
Uid=grimpuser;
Pwd=somePassword;"
>
I get this:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
>
I've tried also with Driver={SQL Native Client} but I get the same
error.

Try using "Provider", rather than "Driver".

--
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

connection error with SQL server 2000

Hello All,
I am working in vwd using C#. In my web develpent I am Connecting to the Sql server 2000's pubs table and displaying result in GridView Control.
My Coding is

SqlConnection con =new SqlConnection("Server=local host;uid=sa;pwd=**secret**;database=pubs");
string str ="select * from authors";
 SqlDataAdapter da =new SqlDataAdapter(str, con);
 DataSet ds =new DataSet();
 da.Fill(ds);
 GridView1.DataSource = ds;
 GridView1.DataBind();

his is a very simple Code.
Now I got a error which is

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)

Can you tell me what is the problem here

rajkumar sharma

Connect using TCP/IP, or use the Surface Area Configuration tool to enable remote connections using named pipes.

|||

I will be thankful if you explain me in some detail.

rajkumar sharma

Connection error

Hi there,

I installed Visual Web Developer Express and SQL Server Express. I then installed the Time Tracker Starter Kit on my localhost. My instance of SQL Server Express is 'Express'. So I updated the root web.config file. I try out the app. by creating a new user and then get this error:

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm using a HTTP connection port 7080 (ie,http://localhost:7080/TimeTracker/), does that mean I have to update anything in the app?

I was told to update SQL Server 2005 Surface Area Configuration - Remote Connections to Using both TCP/IP and named pipes. So I already did that and rebooted.

Any suggestions?

Post your connection string, it may help|||

Hey it works.

I noticed that I had to modify the database connnection under the database explorer, even though I had updated the web.config

Take care

connection error

Hi guys, my asp.net web page was working fine on my laptop. Until i transferred it into my friend's.. an error msg popped out. My SQL Northwind database was been copied over but still there seems to be a problem with the connection, can any one help me?
Personal guess: You are using an sa username/password in the connection string, and the other server you are connecting to is not set up for SQL security, only Windows security.
Show the connection string you are using (with any passwords obscured, of course).
|||

SqlConnection conn=new SqlConnection("server=localhost;uid=sa;pwd=;database=Northwind");

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM tblDoor";

conn.Open();
//how to find out what user id to use?

|||Do you have SQL Server or MSDE? If SQL Server, then you should be able to use Enterprise Manager to try and figure out what the SA password is, as well as determining whether SQL Server security is enabled. If you have Enterprise manager, right click the SQL Server and select Porperties. In the properties window, go to the Security tab make sure authentication is set to SQL Server and Windows.|||oh it is working already, thanks man!

Connection count gone wild after upgrade to SQL2k5!

We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
averaged 100 connections to the server from our web servers and in-house
applications. All we did was update our application config files so the
connection strings pointed at the new server and now we are up to over
5000!!!!! connections. All of our connection strings are either at the
default max connections or lowered to 50-75. We only have 2 web servers
hitting this SQL box and probably a couple dozen workstations running
windows apps in a very subdued manner, i.e. not db intensive at all.
How could this happen? I'm tempted to alter the server config and force a
max# connections but I'm worried what will happen if I do.
Any input/ideas?How do you count the number of connections?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
> averaged 100 connections to the server from our web servers and in-house
> applications. All we did was update our application config files so the
> connection strings pointed at the new server and now we are up to over
> 5000!!!!! connections. All of our connection strings are either at the
> default max connections or lowered to 50-75. We only have 2 web servers
> hitting this SQL box and probably a couple dozen workstations running
> windows apps in a very subdued manner, i.e. not db intensive at all.
> How could this happen? I'm tempted to alter the server config and force a
> max# connections but I'm worried what will happen if I do.
> Any input/ideas?
>|||Perfmon - SQLServer:General Statistics -> User Connections
"Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in message
news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> How do you count the number of connections?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
>> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
>> averaged 100 connections to the server from our web servers and in-house
>> applications. All we did was update our application config files so the
>> connection strings pointed at the new server and now we are up to over
>> 5000!!!!! connections. All of our connection strings are either at the
>> default max connections or lowered to 50-75. We only have 2 web servers
>> hitting this SQL box and probably a couple dozen workstations running
>> windows apps in a very subdued manner, i.e. not db intensive at all.
>> How could this happen? I'm tempted to alter the server config and force
>> a max# connections but I'm worried what will happen if I do.
>> Any input/ideas?
>|||Are you using Service Broker activation by any chance? This would include
also features like SqlDependency or Web pages SqlCacheDependency that are
using activation behind the scenes. Activation can cause the perf counter to
go to abnormaly high values, but is only a perfcounter issue (is not
decremented properly).
You can confirm if the connections really grow or is just the a counter
problem by using a management view, like sys.dm_exec_connections.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O8JnogIoGHA.4728@.TK2MSFTNGP03.phx.gbl...
> Perfmon - SQLServer:General Statistics -> User Connections
> "Remus Rusanu [MSFT]" <remus.rusanu@.online.microsoft.com> wrote in message
> news:uBVNBWIoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> How do you count the number of connections?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23VDKUMIoGHA.4800@.TK2MSFTNGP04.phx.gbl...
>> We upgraded our server last week to SQL 2005. Prior to this on SQL2k we
>> averaged 100 connections to the server from our web servers and in-house
>> applications. All we did was update our application config files so the
>> connection strings pointed at the new server and now we are up to over
>> 5000!!!!! connections. All of our connection strings are either at the
>> default max connections or lowered to 50-75. We only have 2 web servers
>> hitting this SQL box and probably a couple dozen workstations running
>> windows apps in a very subdued manner, i.e. not db intensive at all.
>> How could this happen? I'm tempted to alter the server config and force
>> a max# connections but I'm worried what will happen if I do.
>> Any input/ideas?
>>
>