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 not working in Clustered Env.

Hi,
We had deployed a .NET application in a non clustered environment with conection pooling and was working fine. Now we had moved to a clustered env and connection pooling stopped working. Is there any cluster aware setting that we need to look at. Please let me know your thoughts.
Thanks in advanceCan you be more specific on what went wrong? Any error code, exception stack will be helpful.sqlsql

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

I have an intranet application that comprises an ASP.Net application
connecting to a SQL Server DB
The application has 150 users. At the moment I am connecting using the
following setup
I have created a MyApp_WebUser user in SQL Server. My connection string,
which is stored in my web.config file, is
<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
and by business objects use this string to make all its connections to the
database. This way, all my connections strings are identical and I can make
use of connection pooling.
However, this is causing some problems in the business domain. My DB
contains a User table in which I have a record of all users, their
application login and their application password. When users log in via a
login page, their username and password are checked against the User table
and they get access to various pages according to their role (I also have a
Role table). There are a number of issues that I need to address here and
would appreciate advice
1) It is a business requirement that all passwords need to change every 30
days and they need to conform to a particular pattern. I figure I could
either, write my own code to enforce this in the application or allow users
to log in using their active directory passwords.
If I use their windows login I would have the advantage of letting Windows
deal with the changing of passwords etc, but would I be right in thinking
that connection string should be changed to
<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;integrated security = SSPI" />.
If this is the case, then my connection string will be different for each
user and I will not be able to take advantage of connection pooling
2) If I stick with my User table and write my own code for enforcing the
password requirements, how could I store their passwords in the db in an
encrypted format?
Advice appreciated
Terry HollandAlso
If I do store my connection string with UserID & Password in web.config
file, how secure is thisd?
Terry|||Hi Terry,
Reposnses below:

> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
NO - you are simply changing your authentication mechanism (you might
want to retain the functionality for roles and basically retain your
user and role tables too). Instead of checking the user name password
against the database, you can use Directory Services to test it against
an active directory (do not use integrated authentication, for minimum
impact to your current application). You will need to change ONLY one
function (the one which checks for the username password in the
database).
The Connection string is totally independent of the users of your
application. It can stay exactly as it is now.

> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
The approach mentioned above will let you use the active directoy
policy, so you won't have to write your own code, but for future
purposes, you should always Hash (instead of encrypting) your
passwords. There is a function available in the FormsAuthentication
class (i think) which allows you to do that.
Third - the database connection string in your web.config file:
- If you want to keep it there, keep it in some encrypted form (instead
of plain text) and decrypt it before using. So you could decrypt it and
cache it for your application to use, every so often.
Regards,
Vaibhav|||What you use for authentication to your application, and what you use to
connect to the database are not related. You can use integrated for
authentication to your application - that has no bearing on your connection
string to the database. So you can use integrated, or not.
Btw, just using integrated in your connection string, means that the
connection will be made as the ASPNET user (or whatever ASP.NET is running
under on that machine). It does not mean the credentials of the current user
of your application will be used. You can have it pass the credentials of
the user with a Web.config setting to tell it to impersonate the current
user.
If you want to roll your own security, .NET provides many encryption
implementation for you to chose from. Check out the
System.Security.Cryptography namespace.
"Terry Holland" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>I have an intranet application that comprises an ASP.Net application
> connecting to a SQL Server DB
> The application has 150 users. At the moment I am connecting using the
> following setup
> I have created a MyApp_WebUser user in SQL Server. My connection string,
> which is stored in my web.config file, is
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
> and by business objects use this string to make all its connections to the
> database. This way, all my connections strings are identical and I can
> make
> use of connection pooling.
> However, this is causing some problems in the business domain. My DB
> contains a User table in which I have a record of all users, their
> application login and their application password. When users log in via a
> login page, their username and password are checked against the User table
> and they get access to various pages according to their role (I also have
> a
> Role table). There are a number of issues that I need to address here and
> would appreciate advice
> 1) It is a business requirement that all passwords need to change every 30
> days and they need to conform to a particular pattern. I figure I could
> either, write my own code to enforce this in the application or allow
> users
> to log in using their active directory passwords.
> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;integrated security = SSPI" />.
> If this is the case, then my connection string will be different for each
> user and I will not be able to take advantage of connection pooling
> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
>
> Advice appreciated
> Terry Holland
>|||Terry,
Not quite...
If you use the active directory to login your users your application may
still utilize integrated security for the sql connection and it won't run as
each individual user unless you put <identity impersonate="true" /> into the
web.config file. Instead the integrated security connection to sql server
will use the ASPNET account that the website is running then .net code with.
At this point which user account your web application will use to connect to
sql server and how you should treat such will differ based on which version
of IIS you are using and based on which version of asp.net you are using.
Could you let us know which versions you're working with?
Regards,
S. Justin Gengo
Web Developer / Programmer
Free code library:
http://www.aboutfortunate.com
"Out of chaos comes order."
Nietzsche
"Terry Holland" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>I have an intranet application that comprises an ASP.Net application
> connecting to a SQL Server DB
> The application has 150 users. At the moment I am connecting using the
> following setup
> I have created a MyApp_WebUser user in SQL Server. My connection string,
> which is stored in my web.config file, is
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
> and by business objects use this string to make all its connections to the
> database. This way, all my connections strings are identical and I can
> make
> use of connection pooling.
> However, this is causing some problems in the business domain. My DB
> contains a User table in which I have a record of all users, their
> application login and their application password. When users log in via a
> login page, their username and password are checked against the User table
> and they get access to various pages according to their role (I also have
> a
> Role table). There are a number of issues that I need to address here and
> would appreciate advice
> 1) It is a business requirement that all passwords need to change every 30
> days and they need to conform to a particular pattern. I figure I could
> either, write my own code to enforce this in the application or allow
> users
> to log in using their active directory passwords.
> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;integrated security = SSPI" />.
> If this is the case, then my connection string will be different for each
> user and I will not be able to take advantage of connection pooling
> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
>
> Advice appreciated
> Terry Holland
>|||Thank you.
I have successfully re-written the function to authenticate users using
Directory Services
"V" <vaibhav.gadodia@.gmail.com> wrote in message
news:1147182064.446995.240040@.j73g2000cwa.googlegroups.com...
> Hi Terry,
> Reposnses below:
>
Windows[vbcol=seagreen]
thinking[vbcol=seagreen]
> NO - you are simply changing your authentication mechanism (you might
> want to retain the functionality for roles and basically retain your
> user and role tables too). Instead of checking the user name password
> against the database, you can use Directory Services to test it against
> an active directory (do not use integrated authentication, for minimum
> impact to your current application). You will need to change ONLY one
> function (the one which checks for the username password in the
> database).
> The Connection string is totally independent of the users of your
> application. It can stay exactly as it is now.
>
> The approach mentioned above will let you use the active directoy
> policy, so you won't have to write your own code, but for future
> purposes, you should always Hash (instead of encrypting) your
> passwords. There is a function available in the FormsAuthentication
> class (i think) which allows you to do that.
> Third - the database connection string in your web.config file:
> - If you want to keep it there, keep it in some encrypted form (instead
> of plain text) and decrypt it before using. So you could decrypt it and
> cache it for your application to use, every so often.
> Regards,
> Vaibhav
>|||Thanks for Vaibhav's informative inputs.
Hi Terry,
I don't think you should change the authenitcation to windows since that'll
different much from your original application design. I think you should
manually do the password change policy in your application. For the
password stored in your custom db, generally, it is recommended to store
password hash rather than clear text to make it securer...
as for data connection string in web.config, ASP.NET has provided
encryption support when deploying the application, here are the msdn
articles discussing on this:
#How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
http://msdn.microsoft.com/library/e...05.asp?frame=tr
ue
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

connection pooling

Hi,
Is there a way to enable connection pooling with SQL Server 2005 with ADO,
OLEDB ? (from c++, not .net)
If client is connecting with the same connection string pooling is
automatically created?
thanksConnection Pooling is implemented by the .NET provider or the OLEDB
provider. AFAIK, the OLEDB provider turns this on by itself. My question is:
why are you using OLE DB to access SQL Server?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Andy" <kc2ine@.yahoo.com> wrote in message
news:eEXb6EPIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there a way to enable connection pooling with SQL Server 2005 with ADO,
> OLEDB ? (from c++, not .net)
> If client is connecting with the same connection string pooling is
> automatically created?
> thanks
>

connection pooling

Hi,
Is there a way to enable connection pooling with SQL Server 2005 with ADO,
OLEDB ? (from c++, not .net)
If client is connecting with the same connection string pooling is
automatically created?
thanks
Connection Pooling is implemented by the .NET provider or the OLEDB
provider. AFAIK, the OLEDB provider turns this on by itself. My question is:
why are you using OLE DB to access SQL Server?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andy" <kc2ine@.yahoo.com> wrote in message
news:eEXb6EPIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is there a way to enable connection pooling with SQL Server 2005 with ADO,
> OLEDB ? (from c++, not .net)
> If client is connecting with the same connection string pooling is
> automatically created?
> thanks
>

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

Hi,
Using VB .NET is there a way to return/track the reason why a connection has
failed to open. I want to be able to track the following reasons for
failure:
1. User doesn't have the correct permissions set to open the database
2. Unable to open the connection because the user setting are wrong, (server
name, username, password)
3. Using the wrong authentication - NT authentication instead of SQL Server
authentication and vis versa
Many thanks
Hello 11Oppidan,
Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
please check my answer there and if you need any further assistance on this
particular issue, please reply to me in that thread so I can follow up with
you in time. Also, please don't post the same question in newsgroups in the
future so that our engineers can work on your question efficiently. Your
understanding and cooperation is appreciated.
If you'd like to troubleshoot the problem from the client computer, I
recommend you perform the following steps:
1. Install SQL client tools on the client computer. Check if the user can
log into SQL server using SQL Query Analyzer (QA). Check if the user can
run the SQL statement successfully.
If the user can log into SQL server using QA, it indicates that the user
has permissions on this SQL server.
Note: The user account can be either Windows account or SQL account.
After log into SQL server in QA, if the user can run the SQL statement
successfully, it indicates that the user has the correct permissions on the
database.
If the user cannot log into SQL server using QA, please try to log into QA
as sa or an administrative user. If the issue still exists, you can refer
to the following article:
How to troubleshoot connectivity issues in SQL Server 2000 (827422)
http://support.microsoft.com/default...B;EN-US;827422
2. Contact Product Support Services (PSS) to get mdactrace tool
If you want more detailed information about ADO connection, I recommend
that you open a Support incident with Microsoft Product Support Services
(PSS) so that a dedicated Support Professional can work with you in a more
timely and efficient manner. If you need any help in this regard, please
let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
3. The output file of "sqldiag Utility".
Please execute the following command at the DOS prompt:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
SQLdiag.exe is a command line utility that puts all the SQL server error
logs and other configuration settings into a text file.
Note:
a. Above command use the default folder. You may need to replace the right
folder which sqldiag.exe locates.
b. The generated log file SQLdiag.txt is located in Log folder.
Check the output file.
4. Windows application log
The following information is for your reference:
Viewing the Windows Application Log
<http://msdn.microsoft.com/library/de...-us/adminsql/a
d_perfmon_5qnn.asp>
Check the Windows Application Log file.
You may send me all output files for research at v-sguo@.Microsoft.com
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Sophie,
Thank you again for your time and respnse to my previous question regarding
the security settings. We solved that user problem thanks to your previous
answer. The problem was the user did not have read access to the master db
and we were querying this to return a list of db names attached to the
server. The dba gave her read permission to the master db and that resolved
the problem.
As a consequence of that problem I have realised that I need to change the
code I use to return a list of dbs names for the user to select, and that
was why I asked this second question.
Maybe this is not the best way to do it - but currently I query the master
db to return a list of attached db names. I then go through these opening
each db to check a certain tbl exists. If it does I know is one of the dbs
the user protentially needs to query and I add the name to a combobox item.
This seems to work fine if the user has the correct permissions and user
settings.
Unfortunately though dbas are not too generous with permissions, and
different companies operate very different security policies! So I want to
be able to establish the reason why a connection fails to open when called
in code (VB .NET), and use a try & catch to trap the reason why this is
happening so I can report it back to the user so they can take the right
action to resolve it. The three reasons I can think of why a connection
might fail to open were.
1. User doesn't have the correct permissions set to access the database
2. Unable to open the connection because the user has incorrectly entered
their settings (server
name, username, password)
3. The user has selected to use the wrong authentication - NT authentication
instead of SQL Server
authentication and vis versa.
So far I can only find how to return the state of the connection e.g.
cnn.State <>ConnectionState.Open then Exit Function
But it would be good if I could also trap an SqlException which indicated
which of these different reasons caused the open call to fail!
Your advice and feedback is much appreciated.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:v8sanEoCFHA.2504@.cpmsftngxa10.phx.gbl...
> Hello 11Oppidan,
> Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
> please check my answer there and if you need any further assistance on
> this
> particular issue, please reply to me in that thread so I can follow up
> with
> you in time. Also, please don't post the same question in newsgroups in
> the
> future so that our engineers can work on your question efficiently. Your
> understanding and cooperation is appreciated.
> If you'd like to troubleshoot the problem from the client computer, I
> recommend you perform the following steps:
> 1. Install SQL client tools on the client computer. Check if the user can
> log into SQL server using SQL Query Analyzer (QA). Check if the user can
> run the SQL statement successfully.
> If the user can log into SQL server using QA, it indicates that the user
> has permissions on this SQL server.
> Note: The user account can be either Windows account or SQL account.
> After log into SQL server in QA, if the user can run the SQL statement
> successfully, it indicates that the user has the correct permissions on
> the
> database.
> If the user cannot log into SQL server using QA, please try to log into QA
> as sa or an administrative user. If the issue still exists, you can refer
> to the following article:
> How to troubleshoot connectivity issues in SQL Server 2000 (827422)
> http://support.microsoft.com/default...B;EN-US;827422
>
> 2. Contact Product Support Services (PSS) to get mdactrace tool
> If you want more detailed information about ADO connection, I recommend
> that you open a Support incident with Microsoft Product Support Services
> (PSS) so that a dedicated Support Professional can work with you in a more
> timely and efficient manner. If you need any help in this regard, please
> let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> 3. The output file of "sqldiag Utility".
> Please execute the following command at the DOS prompt:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
> SQLdiag.exe is a command line utility that puts all the SQL server error
> logs and other configuration settings into a text file.
> Note:
> a. Above command use the default folder. You may need to replace the right
> folder which sqldiag.exe locates.
> b. The generated log file SQLdiag.txt is located in Log folder.
>
> Check the output file.
>
> 4. Windows application log
> The following information is for your reference:
> Viewing the Windows Application Log
> <http://msdn.microsoft.com/library/de...-us/adminsql/a
> d_perfmon_5qnn.asp>
> Check the Windows Application Log file.
> You may send me all output files for research at v-sguo@.Microsoft.com
> I hope above information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
>
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
>
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default...national.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
|||Hi 11Oppidan,
As for getting the error info when fail to establish a sqlconnection, I
think the Exception throwed by the runtime
has containded some useful infos. For example:
When we create a new SqlConnection with invalid connectionstring such as
SqlConnection conn = new SqlConnection("#$#@.4234#@.$#@.$");
We'll Receive a "System.ArgumentException" indicate that we've provided
incorrect paratmeters
If the connstring is valid on syntax, then when we call Connection.Open,
the runtime will also throw SqlException with the proper exception message
according to the actual error. For example:
if we use a incorrect login user(username and password), it will return a
SqlException and its
"Message" property will contain
"Login failed for user 'XXXX'."
Also, if we input the wrong server name, the error Message may be something
like:
"SqlServer dosn't exist or access denied."
So generally, the normal way is to add try...catch block around our sql
code and read the Exception.Message property to get the error general error
info.
BTW, as for the 3rd reason you mentioned:
===================
3. The user has selected to use the wrong authentication - NT
authentication
instead of SQL Server
authentication and vis versa.
============================
I'm afraid the runtime (also sqlserver) may not support such checking. The
database will only valdating the comming request according to the request's
authentication type, but won't check whether it is using the wrong
authentication type or whether it may succeed through another type.
HTH. Thanks.
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Connection Open Exemptions

Hi,
Using VB .NET is there a way to return/track the reason why a connection has
failed to open. I want to be able to track the following reasons for
failure:
1. User doesn't have the correct permissions set to open the database
2. Unable to open the connection because the user setting are wrong, (server
name, username, password)
3. Using the wrong authentication - NT authentication instead of SQL Server
authentication and vis versa
Many thanksHello 11Oppidan,
Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
please check my answer there and if you need any further assistance on this
particular issue, please reply to me in that thread so I can follow up with
you in time. Also, please don't post the same question in newsgroups in the
future so that our engineers can work on your question efficiently. Your
understanding and cooperation is appreciated.
If you'd like to troubleshoot the problem from the client computer, I
recommend you perform the following steps:
1. Install SQL client tools on the client computer. Check if the user can
log into SQL server using SQL Query Analyzer (QA). Check if the user can
run the SQL statement successfully.
If the user can log into SQL server using QA, it indicates that the user
has permissions on this SQL server.
Note: The user account can be either Windows account or SQL account.
After log into SQL server in QA, if the user can run the SQL statement
successfully, it indicates that the user has the correct permissions on the
database.
If the user cannot log into SQL server using QA, please try to log into QA
as sa or an administrative user. If the issue still exists, you can refer
to the following article:
How to troubleshoot connectivity issues in SQL Server 2000 (827422)
http://support.microsoft.com/defaul...KB;EN-US;827422
2. Contact Product Support Services (PSS) to get mdactrace tool
If you want more detailed information about ADO connection, I recommend
that you open a Support incident with Microsoft Product Support Services
(PSS) so that a dedicated Support Professional can work with you in a more
timely and efficient manner. If you need any help in this regard, please
let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
3. The output file of "sqldiag Utility".
Please execute the following command at the DOS prompt:
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
SQLdiag.exe is a command line utility that puts all the SQL server error
logs and other configuration settings into a text file.
Note:
a. Above command use the default folder. You may need to replace the right
folder which sqldiag.exe locates.
b. The generated log file SQLdiag.txt is located in Log folder.
Check the output file.
4. Windows application log
The following information is for your reference:
Viewing the Windows Application Log
<http://msdn.microsoft.com/library/d...n-us/adminsql/a
d_perfmon_5qnn.asp>
Check the Windows Application Log file.
You may send me all output files for research at v-sguo@.Microsoft.com
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Sophie,
Thank you again for your time and respnse to my previous question regarding
the security settings. We solved that user problem thanks to your previous
answer. The problem was the user did not have read access to the master db
and we were querying this to return a list of db names attached to the
server. The dba gave her read permission to the master db and that resolved
the problem.
As a consequence of that problem I have realised that I need to change the
code I use to return a list of dbs names for the user to select, and that
was why I asked this second question.
Maybe this is not the best way to do it - but currently I query the master
db to return a list of attached db names. I then go through these opening
each db to check a certain tbl exists. If it does I know is one of the dbs
the user protentially needs to query and I add the name to a combobox item.
This seems to work fine if the user has the correct permissions and user
settings.
Unfortunately though dbas are not too generous with permissions, and
different companies operate very different security policies! So I want to
be able to establish the reason why a connection fails to open when called
in code (VB .NET), and use a try & catch to trap the reason why this is
happening so I can report it back to the user so they can take the right
action to resolve it. The three reasons I can think of why a connection
might fail to open were.
1. User doesn't have the correct permissions set to access the database
2. Unable to open the connection because the user has incorrectly entered
their settings (server
name, username, password)
3. The user has selected to use the wrong authentication - NT authentication
instead of SQL Server
authentication and vis versa.
So far I can only find how to return the state of the connection e.g.
cnn.State <>ConnectionState.Open then Exit Function
But it would be good if I could also trap an SqlException which indicated
which of these different reasons caused the open call to fail!
Your advice and feedback is much appreciated.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:v8sanEoCFHA.2504@.cpmsftngxa10.phx.gbl...
> Hello 11Oppidan,
> Is this a duplicated post of "Security and Logins with ADO.NET"? If so,
> please check my answer there and if you need any further assistance on
> this
> particular issue, please reply to me in that thread so I can follow up
> with
> you in time. Also, please don't post the same question in newsgroups in
> the
> future so that our engineers can work on your question efficiently. Your
> understanding and cooperation is appreciated.
> If you'd like to troubleshoot the problem from the client computer, I
> recommend you perform the following steps:
> 1. Install SQL client tools on the client computer. Check if the user can
> log into SQL server using SQL Query Analyzer (QA). Check if the user can
> run the SQL statement successfully.
> If the user can log into SQL server using QA, it indicates that the user
> has permissions on this SQL server.
> Note: The user account can be either Windows account or SQL account.
> After log into SQL server in QA, if the user can run the SQL statement
> successfully, it indicates that the user has the correct permissions on
> the
> database.
> If the user cannot log into SQL server using QA, please try to log into QA
> as sa or an administrative user. If the issue still exists, you can refer
> to the following article:
> How to troubleshoot connectivity issues in SQL Server 2000 (827422)
> http://support.microsoft.com/defaul...KB;EN-US;827422
>
> 2. Contact Product Support Services (PSS) to get mdactrace tool
> If you want more detailed information about ADO connection, I recommend
> that you open a Support incident with Microsoft Product Support Services
> (PSS) so that a dedicated Support Professional can work with you in a more
> timely and efficient manner. If you need any help in this regard, please
> let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> 3. The output file of "sqldiag Utility".
> Please execute the following command at the DOS prompt:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Binn\sqldiag.exe"
> SQLdiag.exe is a command line utility that puts all the SQL server error
> logs and other configuration settings into a text file.
> Note:
> a. Above command use the default folder. You may need to replace the right
> folder which sqldiag.exe locates.
> b. The generated log file SQLdiag.txt is located in Log folder.
>
> Check the output file.
>
> 4. Windows application log
> The following information is for your reference:
> Viewing the Windows Application Log
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a[/ur
l]
> d_perfmon_5qnn.asp>
> Check the Windows Application Log file.
> You may send me all output files for research at v-sguo@.Microsoft.com
> I hope above information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
>
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
>
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>|||Hi 11Oppidan,
As for getting the error info when fail to establish a sqlconnection, I
think the Exception throwed by the runtime
has containded some useful infos. For example:
When we create a new SqlConnection with invalid connectionstring such as
SqlConnection conn = new SqlConnection("#$#@.4234#@.$#@.$");
We'll Receive a "System.ArgumentException" indicate that we've provided
incorrect paratmeters
If the connstring is valid on syntax, then when we call Connection.Open,
the runtime will also throw SqlException with the proper exception message
according to the actual error. For example:
if we use a incorrect login user(username and password), it will return a
SqlException and its
"Message" property will contain
"Login failed for user 'XXXX'."
Also, if we input the wrong server name, the error Message may be something
like:
"SqlServer dosn't exist or access denied."
So generally, the normal way is to add try...catch block around our sql
code and read the Exception.Message property to get the error general error
info.
BTW, as for the 3rd reason you mentioned:
===================
3. The user has selected to use the wrong authentication - NT
authentication
instead of SQL Server
authentication and vis versa.
============================
I'm afraid the runtime (also sqlserver) may not support such checking. The
database will only valdating the comming request according to the request's
authentication type, but won't check whether it is using the wrong
authentication type or whether it may succeed through another type.
HTH. Thanks.
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Connection OLAP with VS.NET

Hello,
I'm trying to connect with Analysis Service's Database and create, see,
drop cubes with ASP.NET (programming in VB.NET). What I need to do this?
(I'm beginner with Analysis Server, but already I knew SQL Server).
Thanks.
Gema.You want ADOMD.NET
See this download:
9209e6ac8ad&DisplayLang=en" target="_blank">http://www.microsoft.com/downloads/...&DisplayLang=en
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gema Snchez" <gema.sanchez@.grupo-episteme.com> wrote in message
news:e3AGeYTXFHA.616@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to connect with Analysis Service's Database and create, see,
> drop cubes with ASP.NET (programming in VB.NET). What I need to do this?
> (I'm beginner with Analysis Server, but already I knew SQL Server).
> Thanks.
> Gema.
>|||Can I connect only with XML? (ADOMD.NET)
Thanks.
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escribi en el men
saje
news:ueh%23%23OYXFHA.2348@.TK2MSFTNGP14.phx.gbl...
> You want ADOMD.NET
> See this download:
>
layLang=en" target="_blank">http://www.microsoft.com/downloads/... />
layLang=en
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Gema Snchez" <gema.sanchez@.grupo-episteme.com> wrote in message
> news:e3AGeYTXFHA.616@.TK2MSFTNGP12.phx.gbl...
see,[vbcol=seagreen]
>|||Hello Dave,
I've installed ADOMD.NET and that is de way that I want. Thanks. If I
have questions, I'll ask here.
Regards.
"Gema Snchez" <gema.sanchez@.grupo-episteme.com> escribi en el mensaje
news:%23aDqR55XFHA.796@.TK2MSFTNGP10.phx.gbl...
> Can I connect only with XML? (ADOMD.NET)
> Thanks.
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escribi en el
mensaje
> news:ueh%23%23OYXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>
layLang=en" target="_blank">http://www.microsoft.com/downloads/... />
layLang=en
> rights.
> see,
this?[vbcol=seagreen]
>

Connection OLAP with VS.NET

Hello,
I'm trying to connect with Analysis Service's Database and create, see,
drop cubes with ASP.NET (programming in VB.NET). What I need to do this?
(I'm beginner with Analysis Server, but already I knew SQL Server).
Thanks.
Gema.
You want ADOMD.NET
See this download:
http://www.microsoft.com/downloads/d...DisplayLang=en
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gema Snchez" <gema.sanchez@.grupo-episteme.com> wrote in message
news:e3AGeYTXFHA.616@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to connect with Analysis Service's Database and create, see,
> drop cubes with ASP.NET (programming in VB.NET). What I need to do this?
> (I'm beginner with Analysis Server, but already I knew SQL Server).
> Thanks.
> Gema.
>
|||Can I connect only with XML? (ADOMD.NET)
Thanks.
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escribi en el mensaje
news:ueh%23%23OYXFHA.2348@.TK2MSFTNGP14.phx.gbl...
> You want ADOMD.NET
> See this download:
>
http://www.microsoft.com/downloads/d...DisplayLang=en
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
>
> "Gema Snchez" <gema.sanchez@.grupo-episteme.com> wrote in message
> news:e3AGeYTXFHA.616@.TK2MSFTNGP12.phx.gbl...
see,
>
|||Hello Dave,
I've installed ADOMD.NET and that is de way that I want. Thanks. If I
have questions, I'll ask here.
Regards.
"Gema Snchez" <gema.sanchez@.grupo-episteme.com> escribi en el mensaje
news:%23aDqR55XFHA.796@.TK2MSFTNGP10.phx.gbl...
> Can I connect only with XML? (ADOMD.NET)
> Thanks.
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escribi en el
mensaje
> news:ueh%23%23OYXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>
http://www.microsoft.com/downloads/d...DisplayLang=en[vbcol=seagreen]
> rights.
> see,
this?
>

Connection Mystery

I am having trouble connecting to an SQL Server from an ASP.NET pagewritten in C#. I have reduced the code in my page to this:
public void Page_Load( object s, EventArgs e ) {

SqlConnection conn = new SqlConnection( "Server=server;User ID=user;Password=pass;" );
conn.Open();

}
and I get the following error:

System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Thisworks for other server/user/pass except the one I am having troublewith. The SQL Server is using SQL & Windows Authentication. I amable to connect from the machine that is running the webserver viaosql, ODBC, and MS Access using the same arguments (server, user, andpass), so I know that the SQL server does exist, and the User/Passwordshould give me access.
Does anyone have any ideas why thisisn't working or what ASP.NET is does differently from the other accessmethods? Any ideas are greatly appreciated.
Thanks,
Mark Daneyou havent specified what database to connect to..check out thiswebsite for connection strings|||I have tried it with and without using a database name in theconnection string. It does not work either way.
I have tried a variety of connection strings options but none of thecombonations seem to work. However they work connecting to adifferent SQL Server, which makes me think that it is something withthis specific SQL Server. However I am able to connect to theserver through osql, ODBC, or MS Access all without a problem, so Idon't belive it is a problem with the SQL Server accepting the login.Since it doesn't seem to be a problem with the connection stringattributes, or the server, I am not sure what could be causing it.
|||are you sure you have the values properly like the server name, database name uid, pwd all correct ? and can you connect to the sql server from query analyzer using the same credentials ?|||

Try this url for the database permissions setup, if no money transactions passes through the site. Hope this helps.

http://duhnetnuke.net/DesktopDefault.aspx?tabid=40

Kind regards,

Gift Peddie

|||

The info in this link uses the Master as the default database for your Asp.net account permissions. If you are still having problems with that, then it could be a bug. Hope this helps.

http://duhnetnuke2.net/Default.aspx?tabid=64

|||I found out what the problem was. The SQL Server I was trying toconnect to didn't have the latest service pack installed. Iupgraded the SQL Server to SP3a and now it works fine.

Connection Manager

I am using a connection manager defined as a '.Net Providers\SqlClient Data Provider'

I choose the .net form since it has the ability to return multiple result sets.

However, when I set the Advanced 'MultipleActiveResultsSets' to True and check OK,

it doesn't save the setting.

This setting is available under the 'All' window, when you add the Connection Manager.

Any clues as to why this feature isn't functional? Or is this a known bug?

I am piping this into a DataConversion Task

SSIS Data Flow adapters will work only with single result sets.

Thanks,

Bob

|||

Thanks for the answer Bob.

I guess what SSIS appears to me to be is an updated DTS, some of the benefits are offset by the need to script advance functionality.

But isn't that what we did with DTS?

I just figured if you include .Net connections then you should support multiple result sets.

Is this a future option?

I am not completely disappointed with SSIS, but the learning curve doesn't appear to have that much of a reward when functionality like this is missing.

Again thanks for answering my question.

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

I can't connect to my sql server 2000 database from Visual Studio using vb.net.

I'm trying to use the gotdotnet quickstart tutorials but they just throw dumb errors all the time ...

For example, I'm trying to perform a simple SELECT query

Dim myConnection As New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlDataAdapter("select * from Authors", myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")

My error - "Too many arguments to 'public sub New()' " for the SQLConnection.

I then tried this

 Dim dbconn As New SqlClient.SqlConnection
 dbconn.ConnectionString = "server=(local);Database=northwind;User ID=sa;trusted_connection=true"
 dbconn.Open()
 Dim myCommand As New SqlDataAdapter("select top 1 categoryname from Categories", dbconn)
 Dim ds As New DataSet
 myCommand.Fill(ds, "Authors")

Now this seems to work but I'm not sure if I need to bind the SELECT information to a textbox or something (response.write(ds) doesn't work.)

Any help appreciated as this is getting bloody annoying!

CheersThe quickest way to see all the data is to open the web form in design view and drop a DataGrid control from the Toolbox window onto the form. Then switch back to code view and add the follwing lines after the Fill:

DataGrid1.DataSource = ds
DataGrid1.DataBind()

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