4

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.

antik
  • 5,282
  • 1
  • 34
  • 47

3 Answers3

2

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.

dmajkic
  • 3,448
  • 1
  • 18
  • 24
  • Thank you for your response. I've added an edit to my question to address your questions. – antik Dec 23 '08 at 00:02
1
    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

From your stacktrace not having a bottom, can we assume that the CDatabase is a global variable? Possibly in a dll?

We found your exact symptoms if attempting disconnect from SQL Server from within the destructor of a global variable.

Using the MDAC ODBC drivers works successully. Moving the code out of the destructor works sucessfully.

It seems something to do with sql native client not liking being called from inside a DllMain.

ajd
  • 11
  • 1
  • FWIW, Sybase explains that "ODBC functions should not be called directly or indirectly from the DllMain function (...) Calling ODBC functions like SQLFreeHandle, SQLFreeConnect, and SQLFreeEnv can create deadlocks" in "Calling ODBC functions from DllMain" chapter of their docs http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbprogramming/pg-odbcdev.html – mloskot Feb 24 '20 at 10:42
1

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.

Thomi
  • 11,647
  • 13
  • 72
  • 110