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

• Microsoft Data Access Components versions 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2, 2.5

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, and you are using a server-side cursor. A forward-only, read-only cursor is required.

RESOLUTION

There are two ways to get around this limitation:

• For a server-side cursor, do not set rowset properties such as DBPROP_UPDATABILITY and DBPROP_IRowsetChange.



• Use a 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

#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"));
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
		
		//Use this line if you want to use a server side cursor
		hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
		
		//Use this line if you want to use a 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 server-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 a 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 : kbATL kbDatabase kbMDAC kbOLEDB kbProvider kbSQLServ kbConsumer kbGrpVCDB kbMDAC210 kbMDAC210SP2 kbMDAC250
Version : WINDOWS:2.1,2.1 (GA),2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbprb