SQL Native Client ODBC application not disconnecting after SQLDisconnect and not pooling?

Question

Background:
I'm working with a program coded in C++ which uses ODBC on SQL Native Client to establish connections to interact with a SQL Server 2000 database.

Problem:
My connections are abstracted into an object which opens a connection when the object is instantiated and closes the connection when the object is destroyed. I can see that the objects are being destroyed: their destructor are firing and inside of these destructors, SQLDisconnect( ConnHandle ) is being called, followed by SQLFreeHandle( SQL_HANDLE_DBC, ConnHandle ); However, watching the connection count using sp_Who2 or the Performance Monitor in SQL shows the connection count increasing without relent, despite these connections being destroyed.

This hasn't proven problematic until executing a chain of functions that runs long enough to create several thousand of these objects and as such, several thousands of connections.

Question:
Has anyone seen anything like this before? What might be causing this? My initial google searches haven't proven very fruitful!

EDIT:
I have verified that SQLDisconnect is returning without error.

Connection pooling is off. In fact, when I attempt to enabling it using SQLSetEnvAttr, my application crashes when the 2nd call to SQLDriverConnect is made.

Answer 1

Check that you are not using connection pooling. If it is turned on, it will cache opened connections for some (configurable) time.

If you are not using connection pooling, then you must check return value of the SQLDisconnect(). You may have some transaction executing or rollbacking that wont let SQL Disconnect() release your connection.

You have more details on how to check for SQLDisconnect errors at MSDN.

Answer 2

I believe I have seen the same issue in an application that uses MFC and ODBC, rather than the SQL native client API directly. Occaisonally my application hangs on shutdown, the stack trace is:

sqlncli!CCriticalSectionNT::Enter
sqlncli!SQLFreeStmt
sqlncli!SQLFreeConnect
sqlncli!SQLFreeHandle
odbc32!UnloadDriver
odbc32!FreeDbc
odbc32!DestroyIDbc
odbc32!FreeIdbc
odbc32!SQLFreeConnect
mfc42!CDatabase::Close
mfc42!CDatabase::Free
mfc42!CDatabase::~CDatabase

Try as I might, I cannot see anything that might cause such a hang. I'd be grateful if anyone can suggest a solution. It seems others have seen similar issues online, but to date I haven't found any solution.