none
Repeat Invoke Parameterized Query will Cause "Multiple-step OLE DB operation generated errors" RRS feed

  • Question

  • Hi,

    I am using Visual C++ 2008 and ADO to access SQL Server. To improve the performance, I use parameterized Query, as below:

    void CTestADOCmdDlg::OnBnClickedButton1()
    {
        // TODO: Add your control notification handler code here
        _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
        _variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);

        ADODB::_ConnectionPtr  Conn1;
        ADODB::_CommandPtr     Cmd1;
        ADODB::_ParameterPtr   Param1;
        ADODB::_RecordsetPtr   Rs1;

        CoInitialize(NULL);

        // Trap any error/exception.
        try
        {
            // Create and Open Connection Object.
            Conn1.CreateInstance( __uuidof( ADODB::Connection) );
            Conn1->ConnectionString = _bstr_t(L"Provider=sqloledb;Data Source=MYPC;Integrated Security=SSPI;");
            Conn1->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
            
            Conn1->Execute(_bstr_t(_T("Create Database TestCmdDB")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
            Conn1->Execute(_bstr_t(_T("Use TestCmdDB")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
            
            Conn1->Execute(_bstr_t(_T("Create Table MyTable (MyID bigint);")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);

            // Create Command Object.
            Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
            Cmd1->ActiveConnection = Conn1;
            Cmd1->CommandText = _bstr_t(L"SELECT * FROM MyTable WHERE MyID = ?");

            for (LONGLONG nIndex = 0; nIndex < 65536; nIndex ++)
            {
                // Create Parameter Object.
                Param1 = Cmd1->CreateParameter( _bstr_t(L""),
                    ADODB::adBigInt,
                    ADODB::adParamInput,
                    -1,
                    _variant_t( (LONGLONG) nIndex) );
                Param1->Value = _variant_t( (LONGLONG) nIndex );
                Cmd1->Parameters->Append( Param1 );

                // Open Recordset Object.
                Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );
               
                // Do something with the recordset

                Rs1->Close();
            }

        }
        catch(_com_error& e)
        {
            CString strError = e.Description();
        }
        catch(...)             
        {  
        }   

        CoUninitialize();
    }

    In the for loop, the first time execution of:

    Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );

    if OK. However, when in the second time exectution, it will raise an exception, the error description is:

    "Multiple-step OLE DB operation generated errors"

    I cannot understand the meaning. How to solve the problem?

    Thanks

    Sunday, September 22, 2019 8:17 AM

All replies

  • Hi

    For this error,  following are two possible causes of this error:

    • In the registry, under the key for an OLE DB provider's CLSID, there may be an entry named OLEDB_SERVICES. If the OLE DB provider that is used to make the ADO connection does not have the OLEDB_SERVICES entry, and ADO tries to set up a property that is not supported by the provider, the error occurs. For more information about this registry entry, see the "Resolution" section.
    • If OLEDB_SERVICES entry exists but there is a problem in the ADO connection string, the error occurs.

    Please refer to the link below to see the details for other possible reason.

    https://support.microsoft.com/en-in/help/2699687/fix-multiple-step-ole-db-operation-generated-errors-error-when-you-ins

    Let me know how it goes

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 8:52 AM
  • Hi, Naveen,

    Thanks. However, it seems not to be the reason to my problem. I can connect and query without using parameterized query. But with parameterized query and ADO Command, then there will be exception. Also I do not use DATETIME and SMALLDATETIME type in my table.

    Sunday, September 22, 2019 9:16 AM
  • The first time you run the command, Cmd1 has one parameter. The next you call it, it has two parameters, since you are reusing the same command object and keep appending to it. I thinking that it could still work on the second round, because the first parameter object should be intact at this point. However, it will absolutely fail when you add parameter 2101, since you cannot have more than 2100 parameters in stored procedure in SQL Server. (And under the covers, this results in a call to sp_excecutesql.)

    And with more properly wtitten code, you should dispose the parameter object at the end of the loop to avoid memory leak. And in that case, you would definitely get into problems already in the second round.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 22, 2019 9:27 AM
  • Hi

    I can see that you are using ADO rather than ADO .Net - looks you mentioned it as VS 2008.

    Could you try the Scenario 1 & 2 mentioned in the below blog. Hopefully that might be leading the issue.

    http://www.adopenstatic.com/faq/80040e21.asp

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Sunday, September 22, 2019 9:45 AM
  • The first time you run the command, Cmd1 has one parameter. The next you call it, it has two parameters, since you are reusing the same command object and keep appending to it. I thinking that it could still work on the second round, because the first parameter object should be intact at this point. However, it will absolutely fail when you add parameter 2101, since you cannot have more than 2100 parameters in stored procedure in SQL Server. (And under the covers, this results in a call to sp_excecutesql.)

    And with more properly wtitten code, you should dispose the parameter object at the end of the loop to avoid memory leak. And in that case, you would definitely get into problems already in the second round.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi, Erland,

    Thank you very much for your hint. I revised my codes and now the problem is fixed. But there are another two problems related to release the resources.

    My new codes are below:

    void CTestADOCmdDlg::OnBnClickedButton1()
    {
        // TODO: Add your control notification handler code here
        _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
        _variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);

        ADODB::_ConnectionPtr  Conn1;
        ADODB::_CommandPtr     Cmd1, Cmd2;
        ADODB::_ParameterPtr   Param1;
        ADODB::_RecordsetPtr   Rs1, Rs2;

        CoInitialize(NULL);

        // Trap any error/exception.
        try
        {
            // Create and Open Connection Object.
            Conn1.CreateInstance( __uuidof( ADODB::Connection) );
            Conn1->ConnectionString = _bstr_t(L"Provider=sqloledb;Data Source=MYPC;Integrated Security=SSPI;");
            Conn1->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
            Conn1->Execute(_bstr_t(_T("Create Database TestCmdDB2")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
            Conn1->Execute(_bstr_t(_T("Use TestCmdDB2")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);
            Conn1->Execute(_bstr_t(_T("Create Table MyTable (MyID bigint);")), NULL, ADODB::adCmdText | ADODB::adExecuteNoRecords);

            // Create Command Object.
            Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
            Cmd1->ActiveConnection = Conn1;
            Cmd1->CommandText = _bstr_t(L"SELECT * FROM MyTable WHERE MyID = ?");

            // Create Parameter Object.
            Param1 = Cmd1->CreateParameter( _bstr_t(L""),
                ADODB::adBigInt,
                ADODB::adParamInput,
                -1,
                _variant_t( (LONGLONG) 0) );
            Param1->Value = _variant_t( (LONGLONG) 0 );
            Cmd1->Parameters->Append( Param1 );

            // Create Command Object.
            Cmd2.CreateInstance( __uuidof( ADODB::Command ) );
            Cmd2->ActiveConnection = Conn1;
            Cmd2->CommandText = _bstr_t(L"INSERT INTO MyTable VALUES(?);");

            // Append parameter
            Cmd2->Parameters->Append( Param1 );

            for (LONGLONG nIndex = 0; nIndex < 65536; nIndex ++)
            {
                //  Set parameter value
                Param1->Value = _variant_t( (LONGLONG) nIndex);

                // Open Recordset Object.
                Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );

                if (Rs1->BOF && Rs1->ADOEOF)
                {
                    Rs2 = Cmd2->Execute( &vtEmpty, &vtEmpty2, ADODB::adCmdText );
                    //  Rs2->Close();   
                    Rs2 = NULL;
                }

                Rs1->Close();
                Rs1 = NULL;
            }

            Param1->Release();
            Param1 = NULL;
            Cmd1->Release();
            Cmd1 = NULL;

            Cmd2->Release();        Cmd2 = NULL;
            Conn1->Close();
            Conn1 = NULL;

        }
        catch( CException *e ) 
        { 
            e->Delete(); 
        }
        catch(_com_error& e) 
        {
            CString strError = e.Description();

        }
        catch(...)             
        {  
        }   

        CoUninitialize();
    }

    Now I have 4 questions(sorry for so many):

    1. Should I receive a recordset for executing a "INSERT INTO" statement" since it does not return any records? Currently I use Rs2 to receive it, though it is useless at all.

    2. I try to close Rs2 gracefully by using Rs2->Close(), but that will cause an exception with description ""Operation is not allowed when the object is closed". However, my Rs2 is not closed yet, I justr try to close it.

    3. I try to release all objects and set them to NULL. However, when executing the following line:

    Cmd1 = NULL;

    I will an exception and the source line is in Release() function of the com object. Why? 

    4. I see some parameterized query use ? as the parameter, while in others, they use parameter like @MyID. Are both correct way to paramerize a query?

    Thank you very much

    Alan



    • Edited by tempc Monday, September 23, 2019 12:34 AM
    Sunday, September 22, 2019 11:38 PM
  • Hi ,

     

    Thank you for your posting  .

     

    Per your description, I think your issue might be more related to  Visual C++ 2008 and ADO .  If so, please post your issue in the corresponding forum .Visual C++ Forum

     

    By the way, when you solve your original post and you have a new issue , please mark helpful replies and then post a new posting. This will make the person who can help you more aware of your needs.

     

    If you have post your issue ,in order to close this thread, please kindly mark helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, September 23, 2019 6:37 AM
  • tempc, don't care about Rachel. She does not seem to which forum she is in. This is the data-access forum, and your questions is certainly on-topic here. Another story is that getting help with data-access code written in native code is not easy, as there are not many people who do that these days. Most people do .NET or Java.

    Myself, I have worked some with "classic" ADO, but only from Visual Basic, which is a more sheltered experience. And I have never liked ADO, which I think is full of flaws. And, as I mentioned, it is outdated by now. If you have the choice, switch to ODBC. This means that you can get support for new features in SQL Server. (But help with ODBC can still be difficult to get.)

    1. Should I receive a recordset for executing a "INSERT INTO" statement" since it does not return any records? Currently I use Rs2 to receive it, though it is useless at all.

    Yes, you must always get all recordsets. Note the word "all". There can be more recordset than you expect. An INSERT statement will generate a row-count message. And if there are triggers, they can also generate rowcounts.

    Most of the time you don't want these rowcounts, and it is a good idea to issue SET NOCOUNT ON as soon as you have opened the connection.

    But you should still get all recordsets, or else ADO may open a second connection behind your back. There can also be errors hiding there.

    2. I try to close Rs2 gracefully by using Rs2->Close(), but that will cause an exception with description ""*Operation is not* allowed when the *object is closed*". However, my Rs2 is not closed yet, I justr try to close it.

    I would guess this is precisely because you have not gotten the recordssets.

    3. I have released all objects and set them to NULL. However, when I go out of my function in Debugger, I will still get an exception and the source line is in Release() function of the com object. Why? With the stack, I cannot see which object invokes Release().

    That may be happening under the covers. ADO is an interface that sits on top of OLE DB, and when you work with OLE DB, you need to call ->Release() for all objects you have created.

    4. I see some parameterized query use ? as the parameter, while in others, they use parameter like @MyID. Are both correct way to paramerize a query?

    I should know this, but I am not hundred per cent sure. But I think you are right. As long as you are talking to SQL Server you can use @foo, @bar etc and then you need create the parameter accordingly. This should work, since in the end the API calls sp_excutesql which accepts those parameters. ? is the general parameter marker that works with any platform: Access, Oracle, Postgres etc.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 23, 2019 9:18 PM
  • I want to post the latest updates of this problem.

    I try to install SQL Server 2012. And till now all tests with SQL Server 2012 will not cause the problem any more.

    I try to use SQL Server 2008 R2 again, then the problem will occur but seems not so frequent as I modifying using the parameterized query.

    I try to check if there are unreleased objects, but cannot find any.

    Now I am making more tests on SQL Server 2012 to see if one day the problem will occurs again.

    Friday, September 27, 2019 9:19 AM
  • I would not expect SQL 2012 to solve the problem, but it if does - great!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 27, 2019 10:59 AM