Connection Pooling

Connection pooling is set on the connection -- you cannot modify it once you've already connected. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Ian Logan@.discussions.microsoft.com> wrote in message news:36ba6025-224c-4418-9a85-a4567d54290f@.discussions.microsoft.com...I am using SQL 2000 and am trying to use sp_setapprole to set up user access. I am also using VB.NET with the MS Patterns and Practices Data Application Library for the database connection.I have discovered that connection pooling has to be set OFF for this SP to work. My question is this: If I have set connection pooling to OFF to run sp_setapprole, can I then set it back to ON and still run in the same session?To explain a little more:Application startsWindows Authentication is used to grant access to a User tableConnection Pooling set to OFFApplication sets the users role with sp_setapproleConnection Pooling set to ONData access is via Data Application LibraryUser enter data, etc...Many thanksIan LoganI am using SQL 2000 and am trying to use sp_setapprole to set up user access. I am also using VB.NET with the MS Patterns and Practices Data Application Library for the database connection.

I have discovered that connection pooling has to be set OFF for this SP to work. My question is this:
If I have set connection pooling to OFF to run sp_setapprole, can I then set it back to ON and still run in the same session?

To explain a little more:
Application starts
Windows Authentication is used to grant access to a User table
Connection Pooling set to OFF
Application sets the users role with sp_setapprole
Connection Pooling set to ON
Data access is via Data Application Library
User enter data, etc...

Many thanks
Ian Logan
|||That's difficult to know without an understanding of the application's architecture and usage patterns. If the application opens and closes a lot of database connections (on the order of >100/minute during peak periods), this can have a huge impact. If the app is low activity or holds connections open for long periods of time, connection pooling is much less important. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Ian Logan@.discussions.microsoft.com> wrote in message news:cc4a415a-cd9e-4abe-8fa5-a793c496412b@.discussions.microsoft.com...AdamI had the feeling that might be the case.What impact would setting the Pooling to OFF have on the application performance?Kind RegardsIan|||Adam

I had the feeling that might be the case.

What impact would setting the Pooling to OFF have on the application performance?

Kind Regards
Ian