ODBC/OLE DB Connection Pool

I am trying to get Connection Pooling to work in the ASP code (which
runs on IIS 5.0 server) and have tried numerous permutations and
combinations but all have failed. Listed are all the connection
strings I have tried. However I cannot seem to get connection pooling
to work ; I know this as the Connection Pool ODBC counters in
performance monitor show up as zero. Result is that intermittently I
get SQL_Server_does_not_exist_or_access_denied error messages in the
IIS log files.

cn.Open "Provider=sqloledb;Data
Source=server_name\instance_name;initial Catalog=XXXX;User
Id=YYYYYY;Password=ZZZZZ;;OLE DB Services=-1"

cn.Open "Provider=sqloledb;Data
Source=server_name\instance_name;initial Catalog=XXXX;User
Id=YYYYYY;Password=ZZZZZ;"

cn.Open = "Data Source='Data_Source';User
ID='YYYYYY';Password='ZZZZZZ';"

Any input on these would really help.

Kevin

Re: ODBC/OLE DB Connection Pool

Kevin Joseph wrote:
> I am trying to get Connection Pooling to work in the ASP code (which
> runs on IIS 5.0 server) and have tried numerous permutations and
> combinations but all have failed. Listed are all the connection
> strings I have tried. However I cannot seem to get connection pooling
> to work ; I know this as the Connection Pool ODBC counters in
> performance monitor show up as zero.

I'm not 100% sure, but I do not think those counters will keep track of
connections in the OLEDB Session pool. You are using a native OLEDB
provider, not the provider for ODBC.

See here for more info. This article, which I admittedly have not read in a
while, may prove me wrong.
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2 .asp

Here are some more articles

http://support.microsoft.com/?scid=kb;en-us;Q176056
http://support.microsoft.com/default.aspx?scid=kb;en-us;1915 72
http://support.microsoft.com/default.aspx?scid=kb;en-us;3246 86

> Result is that intermittently I
> get SQL_Server_does_not_exist_or_access_denied error messages in the
> IIS log files.

Why do you think that connection pooling has anything to do with this
problem? Lack of connection pooling may result in an excess number of
connections being opened to your database, which may generate "too many
connections" errors, not network-related "not exists" and "access-denied"
errors.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

OLE DB connection pooling is not the ODBC counter. You do not need to force
OLE DB connection pooling with the OLE DB Services=-1 setting, as most
providers already have this on. e.g., look the the SQLOLEDB class ID in the
registry:

HKCR\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}, and you will see a DWORD
key called OLE DB Services set to -1 (0xffffffff).

To get OLE DB connection pooling to work, an instance of the OLE DB
interface, IDataInitialize must be maintained. In the ADO world, this
translates to - you must keep at least one ADO connection around. In the
past, I have used the MDAC simple provider (since it is not a connection to
any real db, but adequetly, it keeps IDataInitialize around).. The
connection string for the Simple Provider is "Provider=MSDAOSP.1;Persist
Security Info=false". Just create an ADO connection with this.

Not sure how to translate this to ASP, since I don't know enough of whether
you have some global place to create a persistent object. I always thought
ASP / IIS did this for you.

Cheers,

Stu

"Kevin Joseph" wrote in message
news:54c48ac0.0411241335.608ad7d7@posting.google.com...
> I am trying to get Connection Pooling to work in the ASP code (which
> runs on IIS 5.0 server) and have tried numerous permutations and
> combinations but all have failed. Listed are all the connection
> strings I have tried. However I cannot seem to get connection pooling
> to work ; I know this as the Connection Pool ODBC counters in
> performance monitor show up as zero. Result is that intermittently I
> get SQL_Server_does_not_exist_or_access_denied error messages in the
> IIS log files.
>
> cn.Open "Provider=sqloledb;Data
> Source=server_name\instance_name;initial Catalog=XXXX;User
> Id=YYYYYY;Password=ZZZZZ;;OLE DB Services=-1"
>
> cn.Open "Provider=sqloledb;Data
> Source=server_name\instance_name;initial Catalog=XXXX;User
> Id=YYYYYY;Password=ZZZZZ;"
>
> cn.Open = "Data Source='Data_Source';User
> ID='YYYYYY';Password='ZZZZZZ';"
>
> Any input on these would really help.
>
> Kevin

Re: ODBC/OLE DB Connection Pool

Stuart,

You are correct about the perfmon counters part - what I meant was
that even when I had the connection string set for ODBC I could not
see those counters rise - which is why I then switched to OLE DB.

Anyway, I have read quite a bit about the latter section in your
e-mail (about IDataInititalize etc) but am not sure how to do that.
Pardon my lack of knowledge in this are but can I have a simple .exe
file running in the background performing this function.

Thanks a lot again for your reply ; really appreciate it.

PS: I have verified basic things like the reg entry etc and they are
good.

Kevin.

"Stuart Carnie" wrote in message news:...
> OLE DB connection pooling is not the ODBC counter. You do not need to force
> OLE DB connection pooling with the OLE DB Services=-1 setting, as most
> providers already have this on. e.g., look the the SQLOLEDB class ID in the
> registry:
>
> HKCR\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}, and you will see a DWORD
> key called OLE DB Services set to -1 (0xffffffff).
>
> To get OLE DB connection pooling to work, an instance of the OLE DB
> interface, IDataInitialize must be maintained. In the ADO world, this
> translates to - you must keep at least one ADO connection around. In the
> past, I have used the MDAC simple provider (since it is not a connection to
> any real db, but adequetly, it keeps IDataInitialize around).. The
> connection string for the Simple Provider is "Provider=MSDAOSP.1;Persist
> Security Info=false". Just create an ADO connection with this.
>
> Not sure how to translate this to ASP, since I don't know enough of whether
> you have some global place to create a persistent object. I always thought
> ASP / IIS did this for you.
>
> Cheers,
>
> Stu
>

Re: ODBC/OLE DB Connection Pool

Kevin Joseph wrote:
> Stuart,
>
> You are correct about the perfmon counters part - what I meant was
> that even when I had the connection string set for ODBC I could not
> see those counters rise - which is why I then switched to OLE DB.
>
> Anyway, I have read quite a bit about the latter section in your
> e-mail (about IDataInititalize etc) but am not sure how to do that.
> Pardon my lack of knowledge in this are but can I have a simple .exe
> file running in the background performing this function.
>
> Thanks a lot again for your reply ; really appreciate it.
>
> PS: I have verified basic things like the reg entry etc and they are
> good.
>
> Kevin.
>
If you read Stuart's post carefully, you will see that you do not have to do
anything to turn Session pooling on. It's on by default. You have to do
extra work to turn it off.

Again, I think you need to look for some other cause for your symptoms. You
seem to be wandering down a blind alley here. I know you are probably just
trying to eliminate all possible problems, but I can say with almost
absolute certainty that turning off pooling will not lead to "server not
found" or "access denied" errors. I've never heard of a situation where this
occurred. If you can point me at one, I would be happy to know about it.

What prompted you to start investigate pooling as a potential cause for this
in the first place? The errors you describe indicate problems with your
network infrastructure, not pooling.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> If you read Stuart's post carefully, you will see that you do not have
> to do anything to turn Session pooling on. It's on by default. You have
> to do extra work to turn it off.

Well, in VB it as easy as:

Publlc Function Button1_Click () As
Dim cnn As new ADODB.Connection
...
Set cnn = Nothing
End Function

And if there is no global ADODB.Connection, there will be no pooling.
(Which Stuart also discussed in his post.)

If the above is possible to do in ASP, I don't know.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books. asp

Re: ODBC/OLE DB Connection Pool

Erland Sommarskog wrote:
> Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
>> If you read Stuart's post carefully, you will see that you do not
>> have to do anything to turn Session pooling on. It's on by default.
>> You have to do extra work to turn it off.
>
> Well, in VB it as easy as:
>
> Publlc Function Button1_Click () As
> Dim cnn As new ADODB.Connection
> ...
> Set cnn = Nothing
> End Function

This does not shut off pooling. Where did you get this idea? Closing thie
connection in this case simply released it to the pool

>
> And if there is no global ADODB.Connection, there will be no pooling.
> (Which Stuart also discussed in his post.)

??
I think you've misinterpreted " ... you must keep at least one ADO
connection around." From all the articles I've read, pooling is always on,
unless you turn it off. From
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/dnmdac/html/pooling2.asp:

"As applications open connections to a given data store and subsequently
release them, a pool of open connections is built with complete
authentication information and connection properties. Then, if available,
connection requests to the same data store (with the same user
authentication and connection properties) are satisfied from the pool rather
than by making the connection on demand. Connections are held open for a
period of time after a consumer application has released them. They are
released by the pooling mechanism when they time out."

So all that needs to happen to initiate a pool is to open a connection.
There appears to be no obligation to keep a connection open. A global
connection object that is never closed until the application closes does
bypass the use of the pool, since the application will never request another
connection. But this does not turn off pooling. If the global connection
object is closed

Now, whether there will be a connection in the pool ready to be used is
another story.


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Just to throw something else in the works - if Application Roles are used in
SQL Server then connection pooling will not work. I forget whether it causes
errors to be raised or if it just doesn't work properly, or which version of
ADO or OLEDB driver it's specific to.

Are Application Roles being used in this case?

"Bob Barrows [MVP]" wrote:

> Erland Sommarskog wrote:
> > Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> >> If you read Stuart's post carefully, you will see that you do not
> >> have to do anything to turn Session pooling on. It's on by default.
> >> You have to do extra work to turn it off.
> >
> > Well, in VB it as easy as:
> >
> > Publlc Function Button1_Click () As
> > Dim cnn As new ADODB.Connection
> > ...
> > Set cnn = Nothing
> > End Function
>
> This does not shut off pooling. Where did you get this idea? Closing thie
> connection in this case simply released it to the pool
>
> >
> > And if there is no global ADODB.Connection, there will be no pooling.
> > (Which Stuart also discussed in his post.)
>
> ??
> I think you've misinterpreted " ... you must keep at least one ADO
> connection around." From all the articles I've read, pooling is always on,
> unless you turn it off. From
> http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/dnmdac/html/pooling2.asp:
>
> "As applications open connections to a given data store and subsequently
> release them, a pool of open connections is built with complete
> authentication information and connection properties. Then, if available,
> connection requests to the same data store (with the same user
> authentication and connection properties) are satisfied from the pool rather
> than by making the connection on demand. Connections are held open for a
> period of time after a consumer application has released them. They are
> released by the pooling mechanism when they time out."
>
> So all that needs to happen to initiate a pool is to open a connection.
> There appears to be no obligation to keep a connection open. A global
> connection object that is never closed until the application closes does
> bypass the use of the pool, since the application will never request another
> connection. But this does not turn off pooling. If the global connection
> object is closed
>
> Now, whether there will be a connection in the pool ready to be used is
> another story.
>
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: ODBC/OLE DB Connection Pool

Good question. I still do not think this is relevant to the OP's real
problem, but it can't hurt to ask.


Mark wrote:
> Just to throw something else in the works - if Application Roles are
> used in
> SQL Server then connection pooling will not work. I forget whether it
> causes
> errors to be raised or if it just doesn't work properly, or which
> version of
> ADO or OLEDB driver it's specific to.
>
> Are Application Roles being used in this case?
>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Hi Bob,

Reason I think the error is related to connection pooling is cos of
the article on MS support site
http://support.microsoft.com/default.aspx?scid=kb;en-us;3284 76

The web site I have servers 20,000+ users. Out of 100,000 hits to the
IIS server around 200 or at most 2000 fail with the "server not found"
error message. The above article clearly states that it is related to
Connection Pool (I have already completed the TCP/IP tasks without
success)

If connection pool is setup on the client, it is possible to monitor
the SQL server connections using perfmon - if the connections grow ;
it means that connection pooling is not working. And this is exactly
what is happening in my case. If you have some other way of proving
that connection pooling is fine ; I will gladly accept your point of
view.

Thanks for the reply Bob, appreciate it.

Kevin.

> If you read Stuart's post carefully, you will see that you do not have to do
> anything to turn Session pooling on. It's on by default. You have to do
> extra work to turn it off.
>
> Again, I think you need to look for some other cause for your symptoms. You
> seem to be wandering down a blind alley here. I know you are probably just
> trying to eliminate all possible problems, but I can say with almost
> absolute certainty that turning off pooling will not lead to "server not
> found" or "access denied" errors. I've never heard of a situation where this
> occurred. If you can point me at one, I would be happy to know about it.
>
> What prompted you to start investigate pooling as a potential cause for this
> in the first place? The errors you describe indicate problems with your
> network infrastructure, not pooling.
>
> Bob Barrows

Re: ODBC/OLE DB Connection Pool

Erland Sommarskog wrote:
> Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
>> If you read Stuart's post carefully, you will see that you do not
>> have to do anything to turn Session pooling on. It's on by default.
>> You have to do extra work to turn it off.
>
> Well, in VB it as easy as:
>
> Publlc Function Button1_Click () As
> Dim cnn As new ADODB.Connection
> ...
> Set cnn = Nothing
> End Function
>
> And if there is no global ADODB.Connection, there will be no pooling.
> (Which Stuart also discussed in his post.)
>
> If the above is possible to do in ASP, I don't know.

Oh wait, It HAS been a while since I read this article. I now see this
paragraph:

************************************************************ **********************
Tips for ADO Users
The ADO Connection object implicitly uses IDataInitialize. However, this
means your application needs to keep at least one instance of a Connection
object instantiated for each unique user-at all times. Otherwise, the pool
will be destroyed when the last Connection object for that string is closed.
(The exception to this is if you use Microsoft Transaction Server. In this
case, the pool is destroyed only if all of the connections in the pool have
been closed by client applications and are allowed to time out.)
Note If you open a Connection object, remember to close it. Do not rely on
garbage collection to implicitly close your connections.
************************************************************ *************


I do question whether this means that the pool won't be recreated the next
time a connection is opened

And check out the exception noted above regarding the use of MTS. In ASP, if
you use Server.CreateObject, you are forcing the use of MTS. Using
vbscript's CreateObject will not cause MTS to be used and you may lose the


And this one (I've added numbers):
************************************************************ ****************
Unintended Disabling of Resource Pooling
Resource pooling can be inadvertently disabled for your application. The
persistence and behavior of resource pooling depend on several conditions
that occur with a given set of user authentication criteria, such as the
following:

1. A given resource pool is specific to the connection attributes of that
set of authentication criteria.
Do not call IDBInitialize::Uninitialize, which implicitly disables pooling.
Instead, use IDBInitialize::Release. While both IDBInitialize::Uninitialize
and IDBInitialize::Release close the connection, calling Uninitialize will
result in the pool being destroyed. (This is not an issue for ADO
developers.)

2. The data source object must be created by using IDataInitialize or
IDBPromptInitialize, and not by CoCreateInstance directly on the OLE DB data
provider. For pools to exist for a given set of connection attributes,
resource pooling requires at least one instance of an OLE DB data source
object created per unique set of connection properties.

3. If using ADO, at least one ADO Connection object must remain open for
each set of user authentication credentials. Although ADO cannot call
Uninitialize, letting go of all ADO Connection objects for a given set of
connection attributes accomplishes the same thing-that is, it releases the
last instance of IDataInitialize.
************************************************************ ********************

The first two are not relevant in ASP. The last one is also not relevant:
ADO is not free-threaded, so it is not recommended that a single global
connection be kept open for the application as this will serialize all
communications with the database.


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Thank you. I now see where you got that idea.


Kevin Joseph wrote:
> Hi Bob,
>
> Reason I think the error is related to connection pooling is cos of
> the article on MS support site
> http://support.microsoft.com/default.aspx?scid=kb;en-us;3284 76
>
> The web site I have servers 20,000+ users. Out of 100,000 hits to the
> IIS server around 200 or at most 2000 fail with the "server not found"
> error message. The above article clearly states that it is related to
> Connection Pool (I have already completed the TCP/IP tasks without
> success)

No, that's not what the article says. It says that one possible outcome from
turning off pooling is the errors you are seeing.

"Note that you can also receive these specific error messages when other
problems are occurring with SQL Server; for example, you may receive these
error messages if the remote computer that is running SQL Server is shut
down, if the remote computer that is running SQL Server is not listening to
TCP/IP sockets at all, if network connectivity to the computer that is
running SQL Server is broken because the network cable is pulled out, or if
you are having DNS resolution issues."

> If connection pool is setup on the client, it is possible to monitor
> the SQL server connections using perfmon - if the connections grow ;
> it means that connection pooling is not working.

I'm not sure that this is the correct conclusion. It may be, but other
conclusions are possible. For example, are you using a single login id for
all your applications that communicate with the database server? If not, you
will get connections for each login id that your applications use. Also,
since your server experiences heavy usage, pooled connections may not be
available when needed, resulting in the creation of more connections.

> And this is exactly
> what is happening in my case. If you have some other way of proving
> that connection pooling is fine ; I will gladly accept your point of
> view.

In IIS 4 and higher, using MDAC2.1 and higher, pooling is on unless you've
turned it off.


I think this section from the article I keep citing is relevant
(http://msdn.microsoft.com/library/default.asp?url=/library/ en-us/dnmdac/html/pooling2.asp):
************************************************************ *******************
Maintaining a Persistent Connection for Resource Pooling
This article has repeatedly emphasized the need for developers who use OLE
DB resource pooling to keep at least one connection open for each set of
user credentials used to access the data store. This is not to be confused
with the mistake of opening up multiple and unnecessary Connection objects,
or even multiple Recordset objects. It's also not to be confused with using
just one Connection object for several hundred Active Server Pages (ASP
pages).

ASP developers should open one connection per set of unique user
credentials. However, an ASP developer can eliminate the benefits of pooling
in this scenario in the following ways:
Instantiating dozens or even hundreds of instances of ADO objects at once.
For example, you can easily circumvent any performance gains found in
pooling by using code such as the following:
Dim cnn(200) As ADODB.Connection
Dim rst(200) As ADODB.Recordset
----------------------------------------------
[my comment]: this is VB code, not vbscript. There is no way this code could
be used in an ASP page. It could, however, be used in a VB COM object being
called by vbscript code in and ASP page. Also, it could be written as:

dim cnn(200)
dim rst(200)

which is perfectly acceptable vbscript code, and the point of this section
would be relevant.
-------------------------------------------------
Presently, these two lines of code appear to be frequently and incorrectly
used on individual ASP pages. The overhead of instantiating, opening, and
manipulating this many objects will eliminate any benefit of pooling, not to
mention that it will swell the pool to unnecessary size just to hold each of
those connections. If you use this technique on multiple ASP pages, which in
turn are hit by multiple users, the amount of memory needed just to hold all
of those ADO objects in memory can soon reach into the gigabytes.

Using just one Connection object for multiple ASP pages.
If you don't want to circumvent the benefits of pooling, however, you can do
this: Within a given ASP page, open the Connection object and one or more
Recordset objects that you need. Then close and delete them.

This also applies for non-ASP developers who are developing an application
with multiple threads. Do not try to share a single Connection object
between all threads. Instead, use one thread, one Connection object, and one
persistent connection in your main application to keep the pool alive.

For Microsoft Transaction Server developers, this is not an issue. Microsoft
Transaction Server itself enables the pool, whether you implement a single
Connection object or not. Because Microsoft Transaction Server is inherently
stateless, trying to keep that persistent Connection object around, let
alone actually use it, is redundant.

************************************************************ **********************

If you use Server.CreateObject, you will be using MTS.


And this section:
************************************************************ ***********************
Top 10 Reasons Why OLE DB Resource Pooling Might Not Work
To summarize what this article has discussed, here is a list of the top 10
reasons why pooling might not be turned on. This text was first made
available in the OLE DB Readme file.

The registry value OLEDB_Services must be present under the provider's
HKEY_CLASSES_ROOT/ key. OLEDB_Services is most commonly set to
0xffffffff, or to desired bits of DBPROPVAL_OS_*. If this does not exist or
is set to 0x00000000, pooling will never occur. For more information, refer
to "Setting Provider Service Defaults" in the OLE DB Services documentation.

------------------------------------------------------------ -
[my comment] None of the following can be done in ASP, except by calling a
COM object in which one of these actions is done
------------------------------------------------------------ -

The consumer can override the OLEDB_Services key and disable pooling by
setting DBPROP_INIT_OLEDBSERVICES.
For more information, refer to "Overriding Provider Service Defaults" in the
OLE DB Services documentation.

The provider must be free-threaded. If a provider developer uses the OLE DB
provider templates for Visual C++ or Visual Basic, the templates will not
create free-threaded providers by default. In those cases, pooling will be
disabled regardless of what the property or registry indicates.

Setting DBPROP_INIT_PROMPT and DBPROMP_INIT_HWND disables pooling of a data
source object. These properties must be set to either VT_EMPTY or NOPROMPT.

Consumers cannot use QueryInterface for any interface unknown to service
components prior to initialization. That is, applications that need to pool
should not use QueryInterface, as COM suggests, to see what interfaces the
provider supports. For example, a simple QI for IDBAsynchStatus, to
determine whether the provider is Asynch or not, eliminates the data source
object from the pool.

Calling IDBProperties::GetPropertyInfo prior to initialization disables
pooling.

If IDBInitialize::UnInitialize is called, the released data source object
will not be pooled.

Pooling does not occur on Windows 95.

Providers must correctly implement aggregation.

Do not use data source object notifications. If you use a QueryInterface for
IConnectionPointContainer to advise a listener to the data source object,
the object will never be pooled.

NOTE These items are cumulative. That is, any item will disable pooling
for that data source object, regardless of what the other values are.
************************************************************ ********************

Bottom line: I think your pooling is turned on, unless some serious problem
exists on your server, or applications are running on your server which are
turning it off.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Kevin Joseph wrote:
> If connection pool is setup on the client, it is possible to monitor
> the SQL server connections using perfmon - if the connections grow ;
> it means that connection pooling is not working. And this is exactly
> what is happening in my case. If you have some other way of proving
> that connection pooling is fine ; I will gladly accept your point of
> view.
>
Darn. I forgot to mention that it is fairly easy to prove that pooling is
working by using SQL Profiler to verify whether your connections are being
re-used.. You can even use sp_who2 to verify that your connections remain in
place for 60 sec. after you web pages use them (Create a test login name.
Create a test page that uses that login. Run the page. Then use sp_who2 in
Query Analyzer to see the connection. Run sp_who2 to verify that the
connection remains. Refresh the page. Use sp_who2 to see if a new connection
gets created).

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ODBC/OLE DB Connection Pool

Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> Darn. I forgot to mention that it is fairly easy to prove that pooling is
> working by using SQL Profiler to verify whether your connections are being
> re-used..

And the thing to look for is calls to sp_reset_connection. If you see this
call, connections are being reused. (If you don't see any calls to
sp_reset_connection, you really don't have any pooling - or connections
occurs less than once a minute.)



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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books. asp

Re: ODBC/OLE DB Connection Pool

Mark (Mark@discussions.microsoft.com) writes:
> Just to throw something else in the works - if Application Roles are
> used in SQL Server then connection pooling will not work. I forget
> whether it causes errors to be raised or if it just doesn't work
> properly, or which version of ADO or OLEDB driver it's specific to.
>
> Are Application Roles being used in this case?

It's rather the other way round. Application roles does not work if you have
pooling on, because you cannot set the application role on a connection
more than once. So you need to turn off pooling by adding OLE DB Services=-2
to the connection string.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books. asp

Re: ODBC/OLE DB Connection Pool

> The first two are not relevant in ASP. The last one is also not relevant:
> ADO is not free-threaded, so it is not recommended that a single global
> connection be kept open for the application as this will serialize all
> communications with the database.

This is incorrect - OLE DB objects are free threaded. It does not serialize
database access - my test application demonstrates this as it is very
multithreaded, and the connections are reused without any problems across
threads ONCE they are returned to the pool.

In actuality, the IDataInitialize object is global in the OLEDB32.dll per
process already. All OLE DB connection objects are constructed through a
serialized call to GetDataSource, however it is minimal overhead.

Having a global object that is not used anywhere else is just fine, as in my
detailed article, it's only purpose is to _initialize_ and retain a
reference to IDataInitialize to enable OLE DB Resource Pooling and nothing
else.

Cheers,

Stu

"Bob Barrows [MVP]" wrote in message
news:ec%23njN%230EHA.3336@TK2MSFTNGP11.phx.gbl...
> Erland Sommarskog wrote:
> > Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> >> If you read Stuart's post carefully, you will see that you do not
> >> have to do anything to turn Session pooling on. It's on by default.
> >> You have to do extra work to turn it off.
> >
> > Well, in VB it as easy as:
> >
> > Publlc Function Button1_Click () As
> > Dim cnn As new ADODB.Connection
> > ...
> > Set cnn = Nothing
> > End Function
> >
> > And if there is no global ADODB.Connection, there will be no pooling.
> > (Which Stuart also discussed in his post.)
> >
> > If the above is possible to do in ASP, I don't know.
>
> Oh wait, It HAS been a while since I read this article. I now see this
> paragraph:
>
>
************************************************************ ****************
******
> Tips for ADO Users
> The ADO Connection object implicitly uses IDataInitialize. However, this
> means your application needs to keep at least one instance of a Connection
> object instantiated for each unique user-at all times. Otherwise, the pool
> will be destroyed when the last Connection object for that string is
closed.
> (The exception to this is if you use Microsoft Transaction Server. In this
> case, the pool is destroyed only if all of the connections in the pool
have
> been closed by client applications and are allowed to time out.)
> Note If you open a Connection object, remember to close it. Do not rely
on
> garbage collection to implicitly close your connections.
> ************************************************************ *************
>
>
> I do question whether this means that the pool won't be recreated the next
> time a connection is opened
>
> And check out the exception noted above regarding the use of MTS. In ASP,
if
> you use Server.CreateObject, you are forcing the use of MTS. Using
> vbscript's CreateObject will not cause MTS to be used and you may lose
the
>
>
> And this one (I've added numbers):
>
************************************************************ ****************
> Unintended Disabling of Resource Pooling
> Resource pooling can be inadvertently disabled for your application. The
> persistence and behavior of resource pooling depend on several conditions
> that occur with a given set of user authentication criteria, such as the
> following:
>
> 1. A given resource pool is specific to the connection attributes of that
> set of authentication criteria.
> Do not call IDBInitialize::Uninitialize, which implicitly disables
pooling.
> Instead, use IDBInitialize::Release. While both
IDBInitialize::Uninitialize
> and IDBInitialize::Release close the connection, calling Uninitialize will
> result in the pool being destroyed. (This is not an issue for ADO
> developers.)
>
> 2. The data source object must be created by using IDataInitialize or
> IDBPromptInitialize, and not by CoCreateInstance directly on the OLE DB da
ta
> provider. For pools to exist for a given set of connection attributes,
> resource pooling requires at least one instance of an OLE DB data source
> object created per unique set of connection properties.
>
> 3. If using ADO, at least one ADO Connection object must remain open for
> each set of user authentication credentials. Although ADO cannot call
> Uninitialize, letting go of all ADO Connection objects for a given set of
> connection attributes accomplishes the same thing-that is, it releases the
> last instance of IDataInitialize.
>
************************************************************ ****************
****
>
> The first two are not relevant in ASP. The last one is also not relevant:
> ADO is not free-threaded, so it is not recommended that a single global
> connection be kept open for the application as this will serialize all
> communications with the database.
>
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: ODBC/OLE DB Connection Pool

Bob, I see you have figured out a lot of this already :)

Anyhow, my article just serves as a detailed description and sample of how
OLE DB Resource Pooling works..

Cheers,

Stu

"Stuart Carnie" wrote in message
news:e7NyG4k1EHA.2568@TK2MSFTNGP11.phx.gbl...
> Bob,
>
> I know nothing about whether IIS creates an instance of the OLE DB
> connection pool (via IDataInitialize) itself, however the code in the
> previous post will 'kill' or disable connection pooling..
>
> IDataInitialize is a singleton class per process, contained within
> OLEDB32.dll. If this is released, then the OLE DB pool is gone.
> IDataInitialize is part of MDAC and aggregates the OLE DB provider's
> IDBInitialize interface, so when it is released, it knows about it and
adds
> it back to the pool.
> The connection string is the KEY used to map back to a connection in the
> resource pool, so for each user in a given process, the resource pool will
> hold a connection. Even if some less significant property is different
(or
> in some cases the order of the connection string text is different)
another
> connection will be added to the pool. An additional thread is created
when
> the OLE DB resource pool is created and initialized, that wakes up every
60
> seconds to check the pool, and release any connections that have not been
> used for (the default) 60 seconds..
>
> In ADO world, IDataInitialize is created when an ADO connection object is
> created, and when the last ADO connection in the current process is
> released, this interface goes away. In a native application using ADO,
one
> can certainly create an instance of IDataInitialize themselves, to enable
> OLE DB connection pooling, however there is no IDispatch interface for
this,
> so no good in the VBScript world.
>
> As an example, in my C++ routines, I construct IDataInitialize and store
it
> in a static variable as follows:
>
> if ( spDataInit == NULL)
> {
> hr = CoCreateInstance(CLSID_MSDAINITIALIZE,
> NULL,
> CLSCTX_INPROC_SERVER,
> IID_IDataInitialize,
> ( void**)&spDataInit);
> if ( FAILED( hr)) return hr;
> }
>
> spDataInit->GetDataSource(NULL, CLSCTX_ALL, L"Provider=MSDAOSP.1",
> IID_IDBInitialize, pDummy);
>
> Which keeps OLE DB Resource Pooling humming.
>
> Another example in Delphi:
>
> const
> MSDAINITIALIZE : TGUID = '{2206CDB2-19C1-11D1-89E0-00C04FD7A829}';
>
> var
> myds : IDBInitialize;
> MyDataInit : IDataInitialize;
>
> procedure CreateIDataInitalize;
>
> begin
>
> OleCheck(CoCreateInstance(MSDAINITIALIZE, nil, CLSCTX_INPROC_SERVER or
> CLSCTX_ALL, IDataInitialize, MyDataInit));
>
> MyDataInit.GetDataSource( nil, CLSCTX_ALL, 'Provider=MSDAOSP.1;Persist
> Security Info=False', IID_IDBInitialize, IInterface( myds));
>
> end;
>
> At least OLE DB provider must be created, hence the call to GetDataSource
on
> the MS Simple Provider, whose only purpose is to initialize the pool, but
it
> is relatively light weight on memory, and does not connect to any real
> database.
>
> Alternatively, a simple way is also to use an ADO connection object, and
> create a connection to the MS Simple Provider. It must be kept around for
> the duration of the process, or at least as long as you want OLE DB
resource
> pooling in effect.
>
> In the link you sent
>
(http://msdn.microsoft.com/library/default.asp?url=/library/ en-us/dnmdac/htm
> l/pooling2.asp), it does mention what I said in my original email. See
the
> section titles "Enabling OLE DB Resource Pooling", you will notice there
is
> reference to and ADO consumer, as follows:
>
> "For an ADO-based consumer, by keeping one open instance of a Connection
> object for each unique user and using the OLEDB_SERVICES connection string
> attribute to enable or disable pooling. By default, ADO attempts to use
> pooling, but if you do not keep at least one instance of a Connection
object
> open for each user, there will not be a persistent pool available to your
> application. (However, Microsoft Transaction Server keeps pools persistent
> as long as the connections in them have not been released and have not
> eventually timed out.) "
>
> This is not entirely true, whilst you do need to keep at least one
instance
> of a Connection object around you do NOT need to keep a connection object
> for each user, as OLE DB resource pooling already does this.. My example
> Delphi or C++ code above works just fine - construct the MSDAOSP provider,
> and then you can use MSSQL as your provider elsewhere. Resource pooling
is
> really quite simple to use and 'just works', the quoted text above makes
it
> seem more complicated that it really is. Just remember my description of
> the IDataInitialize interface above - the whole OLE DB / ADO connection
> string is the KEY used to map back to available connections in the
resource
> pool.
>
> e.g.
> "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
ID=sa"
>
> is treated differently to
>
> "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
ID=SA"
>
> where I have changed the case of SA, despite the fact SQL Server treats
> these the same.
>
> I have written a fairly comprehensive test application (in Delphi) for
> testing our companies own OLE DB provider, and observed that if I comment
> out the above code to initialize the OLE DB resource pool, I can create
and
> destroy about 160 ADO connections to my SQL Server box in roughly 5
seconds,
> using the native MSSQL provider (SQLOLEDB). If I uncomment the code
above,
> I create about 4000 connections (because the same DB connection is being
> reused).. This also works if I use different connection strings.
>
> Here is a pretty good demonstration of OLE DB connection pooling in
> vbscript... The attachment is named .TXT, so please rename to .WSF before
> running, and change the Data Source in the connection strings constants at
> the top of the file to point to your own SQL Server. It also assumes you
> have the pubs database installed.
>
> Another constant, cbEnablePool is set to true by default. If you set this
> to false, the MS Simple Provider is not created and opened, and shows that
> infact OLE DB pooling is not enabled by default, if you do not persist a
> reference to IDataInitalize and initialize the pool.
>
> It does two tests and there are numerous MsgBox's instructing the user to
> run SP_WHO2 in Query Analyzer to monitor the number of connections.
>
> ConnectTest1 shows that if you have cbEnablePool set to true, and connect
to
> the DB, close and set the connection to nothing, you will see a connection
> to the DB remains if you run SP_WHO2 in QA. Then, it will connect again,
> and you will see the connection count is still 1, indicating it reused the
> connection.
> IF cbEnablePool is set to false, you will see the connection is gone when
> running SP_WHO2 after it is closed the first time and set to nothing,
> demonstrating the OLE DB connection pooling is disabled.
>
> ConnectTest2 shows the sensitivity of the connection string, but merely
> changing the User ID=sa to uppercase (User ID=SA), and now 2 connections
> will be present, rather than reusing the original!
>
>
> Cheers and hope this helps.
>
> Stu
>
> "Bob Barrows [MVP]" wrote in message
> news:%23car3Z00EHA.1204@TK2MSFTNGP10.phx.gbl...
> > Erland Sommarskog wrote:
> > > Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> > >> If you read Stuart's post carefully, you will see that you do not
> > >> have to do anything to turn Session pooling on. It's on by default.
> > >> You have to do extra work to turn it off.
> > >
> > > Well, in VB it as easy as:
> > >
> > > Publlc Function Button1_Click () As
> > > Dim cnn As new ADODB.Connection
> > > ...
> > > Set cnn = Nothing
> > > End Function
> >
> > This does not shut off pooling. Where did you get this idea? Closing
thie
> > connection in this case simply released it to the pool
> >
> > >
> > > And if there is no global ADODB.Connection, there will be no pooling.
> > > (Which Stuart also discussed in his post.)
> >
> > ??
> > I think you've misinterpreted " ... you must keep at least one ADO
> > connection around." From all the articles I've read, pooling is always
on,
> > unless you turn it off. From
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/dnmdac/html/pooling2.asp:
> >
> > "As applications open connections to a given data store and subsequently
> > release them, a pool of open connections is built with complete
> > authentication information and connection properties. Then, if
available,
> > connection requests to the same data store (with the same user
> > authentication and connection properties) are satisfied from the pool
> rather
> > than by making the connection on demand. Connections are held open for a
> > period of time after a consumer application has released them. They are
> > released by the pooling mechanism when they time out."
> >
> > So all that needs to happen to initiate a pool is to open a connection.
> > There appears to be no obligation to keep a connection open. A global
> > connection object that is never closed until the application closes does
> > bypass the use of the pool, since the application will never request
> another
> > connection. But this does not turn off pooling. If the global connection
> > object is closed
> >
> > Now, whether there will be a connection in the pool ready to be used is
> > another story.
> >
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
>
>
>

Re: ODBC/OLE DB Connection Pool

Stuart Carnie wrote:
> Bob,
>
> I know nothing about whether IIS creates an instance of the OLE DB
> connection pool (via IDataInitialize) itself, however the code in the
> previous post will 'kill' or disable connection pooling..

Yes. I realize that now. You missed my followup post.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ODBC/OLE DB Connection Pool

Stuart Carnie wrote:
>> The first two are not relevant in ASP. The last one is also not
>> relevant: ADO is not free-threaded, so it is not recommended that a
>> single global connection be kept open for the application as this
>> will serialize all communications with the database.
>
> This is incorrect - OLE DB objects are free threaded. It does not
> serialize database access - my test application demonstrates this as
> it is very multithreaded, and the connections are reused without any
> problems across threads ONCE they are returned to the pool.

That may be so, but according to msdn, ADO installs itself as
apartment-threaded, unless the registry setting is made to make it
both-threaded. This is well-documented at msdn.microsoft.com/library (look
up makfre15.bat). See:
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/dnasp/html/asptips.asp
(the tip against caching connection in Application and Session)

According to that article, using a connection cached in Application (which
is really the only way to accomplish the goal of having a single global
connection in ASP) causes all database access to be serialized.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ODBC/OLE DB Connection Pool

I should clarify this a little more by supporting you that you are correct,
ADO is not free threaded, but most OLE DB providers are. OLE DB handles the
resource pooling and will only pool the OLE DB provider connections if the
OLE DB provider indicates is supports a Free Threaded model
(DBPROP_DSOTHREADMODEL is an OLE DB property, published by individual
providers).. If the provider does not support this, it will not be pooled.

In my example, the global object is created and never used, this could also
be an ADO connection object, and again, it should not be used anywhere
else..

"Stuart Carnie" wrote in message
news:urSqgDl1EHA.3468@TK2MSFTNGP14.phx.gbl...
> > The first two are not relevant in ASP. The last one is also not
relevant:
> > ADO is not free-threaded, so it is not recommended that a single global
> > connection be kept open for the application as this will serialize all
> > communications with the database.
>
> This is incorrect - OLE DB objects are free threaded. It does not
serialize
> database access - my test application demonstrates this as it is very
> multithreaded, and the connections are reused without any problems across
> threads ONCE they are returned to the pool.
>
> In actuality, the IDataInitialize object is global in the OLEDB32.dll per
> process already. All OLE DB connection objects are constructed through a
> serialized call to GetDataSource, however it is minimal overhead.
>
> Having a global object that is not used anywhere else is just fine, as in
my
> detailed article, it's only purpose is to _initialize_ and retain a
> reference to IDataInitialize to enable OLE DB Resource Pooling and nothing
> else.
>
> Cheers,
>
> Stu
>
> "Bob Barrows [MVP]" wrote in message
> news:ec%23njN%230EHA.3336@TK2MSFTNGP11.phx.gbl...
> > Erland Sommarskog wrote:
> > > Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> > >> If you read Stuart's post carefully, you will see that you do not
> > >> have to do anything to turn Session pooling on. It's on by default.
> > >> You have to do extra work to turn it off.
> > >
> > > Well, in VB it as easy as:
> > >
> > > Publlc Function Button1_Click () As
> > > Dim cnn As new ADODB.Connection
> > > ...
> > > Set cnn = Nothing
> > > End Function
> > >
> > > And if there is no global ADODB.Connection, there will be no pooling.
> > > (Which Stuart also discussed in his post.)
> > >
> > > If the above is possible to do in ASP, I don't know.
> >
> > Oh wait, It HAS been a while since I read this article. I now see this
> > paragraph:
> >
> >
>
************************************************************ ****************
> ******
> > Tips for ADO Users
> > The ADO Connection object implicitly uses IDataInitialize. However, this
> > means your application needs to keep at least one instance of a
Connection
> > object instantiated for each unique user-at all times. Otherwise, the
pool
> > will be destroyed when the last Connection object for that string is
> closed.
> > (The exception to this is if you use Microsoft Transaction Server. In
this
> > case, the pool is destroyed only if all of the connections in the pool
> have
> > been closed by client applications and are allowed to time out.)
> > Note If you open a Connection object, remember to close it. Do not
rely
> on
> > garbage collection to implicitly close your connections.
> >
************************************************************ *************
> >
> >
> > I do question whether this means that the pool won't be recreated the
next
> > time a connection is opened
> >
> > And check out the exception noted above regarding the use of MTS. In
ASP,
> if
> > you use Server.CreateObject, you are forcing the use of MTS. Using
> > vbscript's CreateObject will not cause MTS to be used and you may lose
> the
> >
> >
> > And this one (I've added numbers):
> >
>
************************************************************ ****************
> > Unintended Disabling of Resource Pooling
> > Resource pooling can be inadvertently disabled for your application. The
> > persistence and behavior of resource pooling depend on several
conditions
> > that occur with a given set of user authentication criteria, such as the
> > following:
> >
> > 1. A given resource pool is specific to the connection attributes of
that
> > set of authentication criteria.
> > Do not call IDBInitialize::Uninitialize, which implicitly disables
> pooling.
> > Instead, use IDBInitialize::Release. While both
> IDBInitialize::Uninitialize
> > and IDBInitialize::Release close the connection, calling Uninitialize
will
> > result in the pool being destroyed. (This is not an issue for ADO
> > developers.)
> >
> > 2. The data source object must be created by using IDataInitialize or
> > IDBPromptInitialize, and not by CoCreateInstance directly on the OLE DB
da
> ta
> > provider. For pools to exist for a given set of connection attributes,
> > resource pooling requires at least one instance of an OLE DB data source
> > object created per unique set of connection properties.
> >
> > 3. If using ADO, at least one ADO Connection object must remain open for
> > each set of user authentication credentials. Although ADO cannot call
> > Uninitialize, letting go of all ADO Connection objects for a given set
of
> > connection attributes accomplishes the same thing-that is, it releases
the
> > last instance of IDataInitialize.
> >
>
************************************************************ ****************
> ****
> >
> > The first two are not relevant in ASP. The last one is also not
relevant:
> > ADO is not free-threaded, so it is not recommended that a single global
> > connection be kept open for the application as this will serialize all
> > communications with the database.
> >
> >
> > Bob Barrows
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
>
>

Re: ODBC/OLE DB Connection Pool

"Bob Barrows [MVP]" wrote in message
news:OHVmJLl1EHA.2568@TK2MSFTNGP10.phx.gbl...
> Stuart Carnie wrote:
> >> The first two are not relevant in ASP. The last one is also not
> >> relevant: ADO is not free-threaded, so it is not recommended that a
> >> single global connection be kept open for the application as this
> >> will serialize all communications with the database.
> >
> > This is incorrect - OLE DB objects are free threaded. It does not
> > serialize database access - my test application demonstrates this as
> > it is very multithreaded, and the connections are reused without any
> > problems across threads ONCE they are returned to the pool.
>
> That may be so, but according to msdn, ADO installs itself as
> apartment-threaded, unless the registry setting is made to make it
> both-threaded. This is well-documented at msdn.microsoft.com/library (look
> up makfre15.bat). See:
>
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/dnasp/html/asptips.asp
> (the tip against caching connection in Application and Session)
>
> According to that article, using a connection cached in Application (which
> is really the only way to accomplish the goal of having a single global
> connection in ASP) causes all database access to be serialized.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Understood that ASP does things differently, and as you mention, it doesn't
support the concept of a global variable / object, in the traditional sence,
so this solution does not apply. Intesting they have their own connection
manager. Seems like simply initializing OLE DB inside ASP would be a very
beneficial addition :).. Doing this inside a dll that is run inside the
DLLHost / IIS process could work, but beyond the scope of this discussion
;-)

Re: ODBC/OLE DB Connection Pool

I guess I replied a little too quickly there ... :-)
Thanks for the extra details. Thew should prove useful sumeday.

Bob Barrows

Stuart Carnie wrote:
> I should clarify this a little more by supporting you that you are
> correct, ADO is not free threaded, but most OLE DB providers are.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ODBC/OLE DB Connection Pool

Bob Barrows [MVP] wrote:
> I guess I replied a little too quickly there ... :-)
> Thanks for the extra details. Thew should prove useful sumeday.
>
errr ...
They should prove useful someday

Re: ODBC/OLE DB Connection Pool

Stuart Carnie (StuartCarnie@newsgroup.nospam) writes:
> "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
> ID=sa"
>
> is treated differently to
>
> "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
> ID=SA"
>
> where I have changed the case of SA, despite the fact SQL Server treats
> these the same.

That depends on the server collation. If the server collation is case-
sensitive - and mines always are - then 'sa' and 'SA' are two different
users.

Thanks for a very interesting post!


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books. asp

Re: ODBC/OLE DB Connection Pool

"Erland Sommarskog" wrote in message
news:Xns95B0EC9C1A6B6Yazorman@127.0.0.1...
> Stuart Carnie (StuartCarnie@newsgroup.nospam) writes:
> > "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
> > ID=sa"
> >
> > is treated differently to
> >
> > "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=pubs;User
> > ID=SA"
> >
> > where I have changed the case of SA, despite the fact SQL Server treats
> > these the same.
>
> That depends on the server collation. If the server collation is case-
> sensitive - and mines always are - then 'sa' and 'SA' are two different
> users.
>
> Thanks for a very interesting post!
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books. asp

Fair enough :) My demonstration was the sensitivity of the string and / or
properties - other things such as one connection containing a Workstation ID
property and another lacking it will also cause a new connection to be
established..

No worries - hope it helps!

Cheers,

Stu