CODEWITHSUNDEEP
oledboledbconnectionsql-server-mars
Ian Boyd
Ian Boyd

Reputation: 257001

How to disable OLE DB from opening additional implicit non-pooled connections?

OLE DB has a sneaky feature where if your current connection is busy, it will silently open more database connections.

With the SQL Server 2005 "native client", Microsoft introduced a feature where one connection could support Multiple Active Record Sets; so that you can officially have multiple recordsets active over a single connection. But they note it's a tricky thing to enabled, which is why it's been opt-in since beta 2 of the feature.

Secret connections? Really?

Microsoft notes this behavior:

Using ADO with SQL Server Native Client

In prior versions of the OLE DB provider, this code would cause an implicit connection to be created on the second execution because only one active set of results could be opened per a single connection. Because the implicit connection was not pooled in the OLE DB connection pool this would cause additional overhead. With the MARS feature exposed by the SQL Server Native Client OLE DB provider, you get multiple active results on the one connection.

It's also noted by John C. Gordon [MSFT] in the Microsoft forums: (archive)

In OLE DB, there is something called an "implicit connection" spawned when a query is performed while an active resultset is still pending. These are not apparent to the user, as the original poster noticed. I know that SQLNCLI, SQLNCLI10 implement these, but it I do not remember if SQLOLEDB does. What then happens is the server has 2 connections and each one has one pending resultset. This is unpleasant when the licensing model you choose is by connection.

It's also noted in a MARS announcement blog entry: (archive)

Using MARS with SQL Native Client [Chris Lee]

The second result set is using a new connection each time it is opened. This clearly has some overhead (and it turns out that the additional connections aren’t pooled, so the overhead is for a full server connection network protocol exchange each time). This is the default behavior for SQLOLEDB and SQL Native Client (OLE DB) – a new implicit connection is spawned when the main connection is busy with a default result set.

It's a feature deep inside OLE DB, designed to make data access easier.

Bonus Chatter

  • OLE DB is a low level, complicated, API
  • ADO is simplified wrapper around OLE DB

How to turn it off?

This automatic creation of secondary connections is obviously not good. It's so not good that the SQL Server ODBC driver does not do it (because it's a feature deep inside OLE DB, and comes to SQL Server's OLE DB drivers for free. It's not a feature inside ODBC).

I want to make sure i'm not doing it. To this end i want the driver to throw an error at the point in my code where i accidentally try to do this.

How can i turn off OLE DB implicit additional connections?

Upvotes: 1

Views: 837

Answers (1)

Ian Boyd
Ian Boyd

Reputation: 257001

The solution is to disable to feature where OLEDB will open multiple connections behind your back.

You can set the Multiple Connections property of your ADO Connection to false to prevent it from implicitly opening 2nd connections when the main connection is busy with a results set.

//Set Connection.Properties["Multiple Connections"] = false
for int i = 0 to Connection.Properties.Count-1
{
    Property prop = Connection.Properties.Item[i];
    if (prop.Name == "Multiple Connections")
    {
        prop.Value = false;
        break;
    }
}

Note that turning off the ability to open a 2nd connection will then mean that code that used to appear to work will suddenly start to fail with the error "An object was open":

OleDbErr.h

//
// MessageId: DB_E_OBJECTOPEN
//
// MessageText:
//
//  An object was open
//
#define DB_E_OBJECTOPEN                  ((HRESULT)0x80040E05L)

Warning

The Multiple Connections property doesn't apply to the ODBC OLEDB driver (MSDASQL), or the Active Directory driver. (ADsDSOObject)

In fact, they very act of querying for the "Multiple Connections" property of the ADsDSOObject (active directory) provider causes it to throw an

"Item could not be found in the collection"

exception later on when running a SELECT query.

Even commenting out the code that sets:

prop.Value = false;

still causes the error.

It's the very act of calling asking for a property that doesn't exist that causes it to fail. That's why we iterate properties, and only set it if we find it: bugs in OLEDB drivers.

Upvotes: 1

Related Questions