Connectioni leak with MDAC 2.82

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).

Is there a knwon bug like this? Is there a way to trace the ADO provider?are you sure you are explicitly closing your connections?|||What is your code that opens and closes connection to the server?|||

Hi, sorry for the delay, the forum alert seems not to work for me either :-)

Yes. I am closing all connections, the program leaves only about 5-10% of the connections behind, not all of them. Also, I run the same code using ODBC provider and that works absolutely fine. I my code there is a chance to leave connection behind in case of error, but than I would see error messages inmy log, which I don't. Here is the code:

_ConnectionPtr
AsyncOpen(struct s_profiles *profile)
{
time_t start;
string sConnStr;
HRESULT hr;
SYSTEMTIME st1,st2;
_ConnectionPtr pConn = NULL;
char msg[1024];
try {
start = time(NULL);
GetSystemTime(&st1); //time before connect
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
pConn->ConnectionTimeout = profile->conn->timeout; //set ADO Connection Timeout (default 30sec)
pConn->CommandTimeout = profile->timeout; //set ADO Command Timeout (default 30sec)

if (pConn->State == adStateOpen) {
nimLog(1,"Profile: %s, connection: %s already opem", profile->name, profile->connection);
return pConn; //Already open
}

if (strcmp(profile->cursor,"server") == 0)
pConn->CursorLocation = adUseServer; //use server cursor
else
pConn->CursorLocation = adUseClient; //use client cursor (default)

sConnStr = CreateConnString(profile->conn);

hr = pConn->Open(_bstr_t(sConnStr.c_str()),bsNull,bsNull,adAsyncConnect);
if (FAILED(hr))
{
nimLog(0,"Profile %s, AsyncOpen - failed for connection '%s'",profile->name, profile->connection);
return pConn;
}

while (pConn->State == adStateConnecting)
{
//nimLog(0,"Profile: %s - Wait for Connection to open", profile->name);
if(time(NULL) > (start + profile->conn->timeout + gSetup->COM_tout_delay))
{
nimLog(0,"Profile %s, AsyncOpen - connection timeout",profile->name;
pConn->Cancel();
pConn = NULL;
return pConn;
}
Sleep(5);
}
nimLog(2,"Profile %s, AsyncOpen - connected to database",profile->name);

if (FAILED(hr))
{
nimLog(0,"Open - failed for connection '%s'",profile->connection);
return 0;
}
}
catch(_com_error &e ) {
nimLog(0,"Profile: %s, Error in AsyncOpen", profile->name);
LogComError(e, profile->name, 0, "Open");
return 0;
}
catch(...){
nimLog(0,"Profile: %s, Unknown error in Open", profile->name);
sprintf(msg,"Profile %s, unknown error in Open", profile->name);
return 0;
}
//return (pConn->State == adStateOpen ? 1 : 0);
return pConn;
}

//close

try
{
hr = pConn->Close();
if (FAILED(hr))
{
nimLog(0,"Close connection failed (%s:%d)",__FILE__,__LINE__);
EXIT_THREAD(0);
}
pConn = NULL;
}

Thanks for help!

|||Is Cancel method same as Close for the ADO Connection in C++? I do not know C++, but if this is the case, then code looks good. What you could do is to run SQL Profiler to trace activity between the server and your application. It could provide additional information as well|||

Close and Cancel are not exactly the same, Cancel is used to close an asynchronous request, Close to end after you're done. I am using asynchronous Open to be able to bail out if timeout reach, than I need Cancel.

To use the Profiler shows, unfortunately only half of the story. I can see what the ADO provider send towards SQL, but not what my application sends to ADO. At the end I can see every once in a whole there is a connection not closed, but no idea why - did the application not sent the close? Not to be seen in profiler. And ODBC like trace does not exeists, at least to my knowledge.

I am believing now there is something wrong with the ADO provider on that one machine, as it doens't happen on others and also it works there with ODBC. The question remains what is wrong, I cannot see anything obvious. I only observed, that the response time with ADO was considerable slower than with ODBC, althoug I would expect it the other way around.

Any other ideas out there?

|||It is possible that Cancel is not enough. Can you try to use Close to see if it helps or you cannot?|||Unfortunately, it is a production machine where it happens, and I am limited in my activities there. But on my test machine Cancel works, quite the oposite, Close doesn't work there. But to this part of code I get only in "timeout" situation, where I get a message - I didn't get any timeout at all yet on the production machine. I would say it must be something else.|||

I am wondering if your ADO connection is actually getting pooled.

Add "Pooling=false;" to your ado connection string. If the problem goes away, its not an issue, remove the keyword/value pair and carry on. If the problem persists, I am wondering if you need an analog to your _ConnectionPtr->CreateInstance that releases the instance?

|||Make sure that the connection is closed--even if the code throws an exception.|||

Hi Dbrich,

Same problem what you faced long back ago.

I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790),SP4 for SQL Server.

actually when i run the application on my machine i didn't find any connection leak but on production machine i find connection leak.

Is there a know bug like this? Is there a way to trace the ADO provider?

In my SQL Profiler I got this error number 16945,16955,208