Microsoft KB Archive/289278

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

Article ID: 289278

Article Last Modified on 11/17/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194

-



This article was previously published under Q289278



SYMPTOMS
SQL Server 2000 Books Online contains a section titled &quot;Rowsets and SQL Server Cursors&quot; 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 &quot;text&quot;, &quot;ntext&quot;, or &quot;image&quot; column data type, the following error will be returned from the ICommand::Execute call:

HRESULT = 0x80040e21 (DB_E_ERRORSOCCURRED)

Description = &quot;Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.&quot;

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 &quot;Implicit Conversion of Fast Forward-only Cursors&quot; which states the following:

&quot;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.&quot;

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



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.



STATUS
This behavior is by design.



MORE INFORMATION
The following code written with the ATL Consumer Template classes demonstrates the problem:
 * 1) include 


 * 1) 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(&quot;SQLOLEDB&quot;, &quot;YourServer&quot;, &quot;sa&quot;, &quot;YourPassword&quot;)); CSession sn; CHECKHR(sn.Open(ds));

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

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

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

Additional query words: DBPROP_REMOVEDELETED BLOB SQLOLEDB implicit conversion multiple-step

Keywords: kbprovider kbdatabase kbprb KB289278

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.