PRB: Setting DBPROP_REMOVEDELETED Property to VARIANT_FALSE Fails to Open Fast-Forward Cursor (289278)

SYMPTOMS

SQL Server 2000 Books Online contains a section titled "Rowsets and SQL Server Cursors" which states that setting the following rowset properties for the SQL Server OLE DB Provider (SQLOLEDB) is equivalent to a fast-forward cursor:

DBPROP_SERVERCURSOR = true
DBPROP_DEFERRED = false
DBPROP_IRowsetChange = false
DBPROP_IRowsetLocate = false
DBPROP_IRowsetScroll = false
DBPROP_IRowsetUpdate = false
DBPROP_BOOKMARKS = false
DBPROP_CANFETCHBACKWARDS = false
DBPROP_CANSCROLLBACKWARDS = false
DBPROP_CANHOLDROWS = false
DBPROP_LITERALBOOKMARKS = false
DBPROP_OTHERINSERT = true
DBPROP_OTHERUPDATEDELETE = true
DBPROP_OWNINSERT = true
DBPROP_OWNUPDATEDELETE = true
DBPROP_QUICKRESTART = false
DBPROP_IRowsetResynch = false
DBPROP_CHANGEINSERTEDROWS = false
DBPROP_SERVERDATAONINSERT = false
DBPROP_UNIQUEROWS = false
DBPROP_REMOVEDELETED = false

However, if all of these properties are set and a rowset contains a "text", "ntext", or "image" column data type, the following error will be returned from the ICommand::Execute call:

HRESULT = 0x80040e21 (DB_E_ERRORSOCCURRED)
Description = "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

A call to ICommandProperties::GetProperties() to retrieve the properties which could not be set (using DBPROPSET_PROPERTIESINERROR) will show that the DBPROP_OTHERINSERT property caused the error.

CAUSE

The fast-forward SQL Server cursor is converted to a dynamic cursor because fast-forward cursors cannot handle text, ntext, or image fields.

SQL Server 2000 Books Online has a section titled "Implicit Conversion of Fast Forward-only Cursors" which states the following:

"If the SELECT statement references text, ntext, or image columns the cursor is converted to a dynamic cursor if the OLE DB Provider for SQL Server or the SQL Server ODBC driver are used."

Because the SQL Server cursor type is converted to dynamic instead of fast-forward, setting DBPROP_REMOVEDELETED to false conflicts with this.

MORE INFORMATION

The following code written with the ATL Consumer Template classes demonstrates the problem:

#include <atldbcli.h>

#define CHECKHR(hr) if ( FAILED(hr)) return -1;

void AssignRowsetProperties(CDBPropSet & ps);

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

	HRESULT hr;
	// Open connection.
	CDataSource ds;
	CHECKHR(ds.Open("SQLOLEDB", "YourServer", "sa", "YourPassword"));
	
	CSession sn;
	CHECKHR(sn.Open(ds));


	////// Open up rowset.
	// First set up properties.
	// 
	CDBPropSet ps;
	AssignRowsetProperties(ps);

	// Open the rowset.
	CCommand<CDynamicAccessor> cmdOpenRowset;
	if (FAILED(hr = cmdOpenRowset.Open(sn, "Select * from TestTable", &ps, NULL, DBGUID_DBSQL, false)))
        // This will dump the error to the output window of the debugger.
	AtlTraceErrorRecords(hr);  

	CComQIPtr<ICommandProperties> pCommandProperties = cmdOpenRowset.m_spCommand;

	DBPROPIDSET rgPropertIDSets[1];
	rgPropertIDSets[0].cPropertyIDs = 0;
	rgPropertIDSets[0].guidPropertySet = DBPROPSET_PROPERTIESINERROR;
	rgPropertIDSets[0].rgPropertyIDs = NULL;

	ULONG ulPropsets;
	DBPROPSET * pPropSet;
	pCommandProperties->GetProperties(1,rgPropertIDSets, &ulPropsets, &pPropSet);
	
	return 0;
}


void AssignRowsetProperties(CDBPropSet & ps)
{
	ps.guidPropertySet = DBPROPSET_ROWSET;
	ps.AddProperty(DBPROP_SERVERCURSOR, true);
	ps.AddProperty(DBPROP_DEFERRED, false);
	ps.AddProperty(DBPROP_IRowsetChange, false);
	ps.AddProperty(DBPROP_IRowsetLocate, false);
	ps.AddProperty(DBPROP_IRowsetScroll, false);
	ps.AddProperty(DBPROP_IRowsetUpdate, false);
	ps.AddProperty(DBPROP_BOOKMARKS, false);
	ps.AddProperty(DBPROP_CANFETCHBACKWARDS, false);
	ps.AddProperty(DBPROP_CANSCROLLBACKWARDS, false);
	ps.AddProperty(DBPROP_CANHOLDROWS, false);
	ps.AddProperty(DBPROP_LITERALBOOKMARKS, false);
	ps.AddProperty(DBPROP_OTHERINSERT, true);
	ps.AddProperty(DBPROP_OTHERUPDATEDELETE, true);
	ps.AddProperty(DBPROP_OWNINSERT, true);
	ps.AddProperty(DBPROP_OWNUPDATEDELETE, true);
	ps.AddProperty(DBPROP_QUICKRESTART, false);
	ps.AddProperty(DBPROP_IRowsetResynch, false);
	ps.AddProperty(DBPROP_CHANGEINSERTEDROWS, false);
	ps.AddProperty(DBPROP_SERVERDATAONINSERT, false);
	ps.AddProperty(DBPROP_UNIQUEROWS, false);

	ps.AddProperty(DBPROP_REMOVEDELETED, false);
}

PRB: Setting DBPROP_REMOVEDELETED Property to VARIANT_FALSE Fails to Open Fast-Forward Cursor (289278)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

MORE INFORMATION