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

View products that this article applies to.

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."

Back to the top

CAUSE

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

Back to the top

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.

Back to the top

STATUS

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

Back to the top

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

Back to the top

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

Back to the top