Hi! I wrote a stored procedure that executes a SELECT query with the "FOR XML" clause. It must run on SQL Server 2000, SQL Server 2005 and SQL Server 2008. How should I call it from "classic ADO" (msado15.dll) ? In fact, the main application which connects to SQL Server is written in Visual C++ 6.0. No, I can't port it to C#. On the Internet I saw Vb6 examples, but no C++ ones. A colleague of mine found an example in a newsgroup, so he wrote a function to do that, but I'm not sure it's 100% correct. Here is it (simplified): m_pCommand is a smart pointer to a Command object, another function initializes it, setting CommandType = adCmdStoredProc and CommandText = the stored procedure name. Parameters are retrieved with Refresh(). void Executor::ExecuteAndGetXml(_bstr_t& bstrOutput){ _variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR); _variant_t vtEmpty2 (vtEmpty); _variant_t vv; try { _bstr_t bstrEmpty(""); _variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR); _StreamPtr pSt; pSt.CreateInstance(__uuidof(Stream)); pSt->Open(vtEmpty,adModeUnknown,adOpenStreamUnspecified,bstrEmpty,bstrEmpty); vv.vt = VT_DISPATCH; vv.pdispVal = pSt.GetInterfacePtr(); m_pCommand->Properties->Item[_variant_t("Output Stream")]->PutValue(vv); m_pCommand->Execute(&vtEmpty,&vtEmpty2,adExecuteStream); bstrOutput = pSt->ReadText(adReadAll); vv.Detach(); pSt->Close(); pSt.Release(); } catch(_com_error &e) { // [CUT] error handling vv.Detach(); // Prevent crash! }}Or "source" is this post on a newsgroup:http://groups.google.it/group/microsoft.public.data.ado/browse_thread/thread/1ace5d9df7f8688a/433c34a50cf7aa4e?hl=it&ie=UTF-8&q=pdispVal+GetInterfacePtr+adExecuteStream#433c34a50cf7aa4eThe Command object is cleaned up by the class destructor: it just calls Release (but I think it's not mandatory, as m_pCommand is a smart pointer).The problem is: testers suspects the program has a memory leak. I used UMDH to inspect it. It shows stack traces like this:+ e0 ( 1c0 - e0) 2 allocs BackTrace23F1+ 1 ( 2 - 1) BackTrace23F1 allocations ntdll!RtlAllocateHeapSlowly+00000041 ntdll!RtlAllocateHeap+00000E9F ole32!operator new+00000016 ole32!CComRegCatalog::GetClassInfoW+000001CE ole32!CComCatalog::GetClassInfoInternal+0000041D ole32!CComCatalog::GetClassInfoW+00000022 ole32!GetClassInfoFromClsid+0000002D ole32!LookForConfiguredClsid+00000033 ole32!ICoCreateInstanceEx+00000135 ole32!CComActivator::DoCreateInstance+0000006A ole32!CoCreateInstanceEx+00000023 ole32!CoCreateInstance+0000003C MYAPPNAME!Executor::ExecuteAndGetXml+000000E0 (D:\Software\source\Executor.cpp, 5616)
Line 5616 corresponds to: pSt.CreateInstance(__uuidof(Stream));
It looks like the Stream object is not deallocated! But this is not all: there are also stack traces like this:+ e0 ( 1c0 - e0) 2 allocs BackTrace1C09+ 1 ( 2 - 1) BackTrace1C09 allocations ntdll!RtlAllocateHeapSlowly+00000041 ntdll!RtlAllocateHeap+00000E9F ole32!operator new+00000016 ole32!CComRegCatalog::GetClassInfoW+000001CE ole32!CComCatalog::GetClassInfoInternal+0000041D ole32!CComCatalog::GetClassInfoW+00000022 ole32!GetClassInfoFromClsid+0000002D ole32!LookForConfiguredClsid+00000033 ole32!ICoCreateInstanceEx+00000135 ole32!CComActivator::DoCreateInstance+0000006A ole32!CoCreateInstanceEx+00000023 ole32!CoCreateInstance+0000003C MYAPPNAME!Executor::CreateCommand+00000051 (D:\Software\source\Executor.cpp, 6512)
As you can guess, 6512 corresponds to another function where the smart pointer m_pCommand is allocated : m_pCommand.CreateInstance(__uuidof(Command))
Why? Is there an error in our Executor::ExecuteAndGetXml? Or can this behavior be caused by some form of connection pooling? I don't think connection pooling in on, because, after the command is executed and my Executor object is destroyed, SQL Profiler shows an Audit Logout from my application, and my application disappears from SQL Server Activity Monitor...Any help will be appreciated! Thank you very much!Ermanno Pejo
Calling a FOR XML query from good old Visual C++ 6
Posted - 2009-11-29 : 08:53:28