Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: Setting DBPROP_REMOVEDELETED Property to VARIANT_FALSE Fails to Open Fast-Forward Cursor

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

View products that this article applies to.

This article was previously published under Q289278

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Resolution

Do not use text, ntext, or image fields in rowsets for which you would like to use fast-forward SQL Server cursors, or change the rowset properties to reflect a dynamic SQL Server cursor.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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

↑ Back to the top

Keywords: KB289278, kbprovider, kbprb, kbdatabase

↑ Back to the top

Article Info
Article ID : 289278
Revision : 4
Created on : 11/17/2003
Published on : 11/17/2003
Exists online : False
Views : 603