What needs to be done to enable pooling in a Delphi 7 app? My connection string is:
Provider=SQLOLEDB.1;Initial Catalog=%s;Data Source=%s;Password=%s;User ID=%s;OLE Db Services=-1
I can tell that connection pooling is not being achieved by looking at the SQLServer:GeneralStatistics UserConnections performance counter - it fluctuates wildly when my application runs. With connection pooling I'd expect it to achieve a steady state. Also, I see that Logins/sec and Logouts/sec counters are both very high - if connection pooling were used Logouts/sec would be at or near zero.
In searching I found this article on resource pooling:
http://www.ddj.com/database/184416942
It suggests that "If you are working at the OLEDB SDK (or COM) level using ATL, you have to write some more code" (aside from adding OLE Db Services=-1 to the connection string) to get connection pooling:
CDataSource db; CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_USERID, "MyName); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, "MyServer); dbinit.AddProperty(DBPROP_INIT_CATALOG, "MyDb ); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_OLEDBSERVICES, (long)DBPROPVAL_OS_ENABLEALL); HRESULT hr = db.OpenWithServiceComponents(_T("sqloledb"), &dbinit);
Unfortunately that code is Greek to me and I'm not sure how to translate that to Delphi (or if its even necessary).
I'm also careful not to change the connection string at all. Any suggestions on what else I might need to do to enable resource pooling?
You need to keep one instance of the connection open at all times...if it drops to zero, then ADO will re-establish the connection to authenticate the user.