Microsoft KB Archive/241639

Knowledge Base

PRB: "Errors Occurred" Error When Calling Stored Procedure with More than a SELECT Statement

Article ID: 241639

Article Last Modified on 5/12/2003

---

APPLIES TO

• Microsoft Data Access Components 2.1
• Microsoft Data Access Components 2.5
• Microsoft Data Access Components 2.6
• Microsoft Data Access Components 2.7

---

This article was previously published under Q241639

SYMPTOMS

When calling ICommand::Execute to execute a Microsoft SQL Server stored procedure using the Microsoft SQL Server OLE DB Provider, DB_E_ERRORSOCCURRED is returned and the IErrorInfo description information states "Errors Occurred."

CAUSE

The stored procedure contains more than a simple SELECT statement. A client side cursor is required.

RESOLUTION

In order to resolve this, you will have to use client side cursors. There are two ways to use client side cursors:

• For a forward only, read only client cursor, do not set rowset properties such as DBPROP_UPDATABILITY and DBPROP_IRowsetChange.
• Use an updateable client-side cursor.

STATUS

This behavior is by design (although Microsoft is investigating whether a more informative error message can be returned).

MORE INFORMATION

Here is an example of what the stored procedure might look like:

CREATE  procedure sp_Test
AS
    DECLARE @var int
    SET  @var = 1
    SELECT * FROM authors
                

Steps to Reproduce Behavior

To reproduce the error, create this sample stored procedure and then execute the following code:

#include <atldbcli.h>

class CTestAccessor
{
public:
    LONG m_RETURNVALUE;

BEGIN_PARAM_MAP(CTestAccessor)
    SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
    COLUMN_ENTRY(1, m_RETURNVALUE)
END_PARAM_MAP()

DEFINE_COMMAND(CTestAccessor, _T("{ ? = CALL dbo.sp_Test}"))


};

class CTestCmd : public CCommand<CAccessor<CTestAccessor> >
{
public:
    HRESULT Open()
    {
        HRESULT     hr;

        hr = OpenDataSource();
        if (FAILED(hr))
            return hr;

        return OpenRowset();
    }
    HRESULT OpenDataSource()
    {
        HRESULT     hr;
        CDataSource db;
        CDBPropSet  dbinit(DBPROPSET_DBINIT);

        dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));
        dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("pubs"));
        //change the name of SQL server to fit your environment
                dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("localhost"));
                dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
        dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
        
        //Use this line if you want to use a forward only, read only client side cursor
        hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
        
        //Use this line if you want to use an updateable client side cursor 
        //hr = db.OpenWithServiceComponents(_T("SQLOLEDB.1"), &dbinit); 
        
        if (FAILED(hr))
            return hr;

        return m_session.Open(db);
    }
    HRESULT OpenRowset()
    {
        // Set properties for open
        CDBPropSet  propset(DBPROPSET_ROWSET);
        
        //Remove the following properties to prevent the error with a forward only, read only client side cursor
        //Set them if you want an updateable client side cursor
        propset.AddProperty(DBPROP_IRowsetChange, true);
        propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
        
        //Set this property if you want to use an updateable client side cursor
        //propset.AddProperty(DBPROP_CLIENTCURSOR, true);

        return CCommand<CAccessor<CTestAccessor> >::Open(m_session, NULL, &propset);
    }
    CSession    m_session;
};

int main(int argc, char* argv[])
{
    CoInitialize(NULL);

    CTestCmd rs;
    HRESULT hr=rs.Open();
    if (FAILED(hr))
            AtlTraceErrorRecords();

    rs.Close();

    return 0;
}
                

REFERENCES

See the SQL Server Books Online topic "Rowsets and SQL Server Cursors" for more information.

Additional query words: updatable rowset multiple resultset cursor engine keyset

Keywords: kbprovider kbconsumer kbdatabase kbprb KB241639

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.