Microsoft KB Archive/241639

= 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:
 * 1) include 

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 > { 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 >::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; }

