Re: Multiple-step OLE DB operation generated errors.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kevin Frey (kevin_g_frey_at_hotmail.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 16:07:35 +1000

OK, I have now discovered the cause of the problem.

If you use an alternate database API (such as ADO or ODBC) rather than
SQLOLEDB directly, and review the results in the SQL Profiler you may come
to notice what are called "CursorImplicitConversion" events. In testing my
queries on ODBC I noticed that when I used an ORDER BY that was failing
through OLE DB with a Dynamic cursor (the "Multiple-step" error) I would in
fact also notice that the same query in ODBC shows a
CursorImplicitConversion event.

What is in fact happening is that the database server is, in some cases,
downgrading or changing the cursor type from what was requested to something
that it can support. In the case of requesting a dynamic cursor, it would
appear that the server can only satisfy the request for a dynamic cursor IF
an ORDER BY clause matches an index.

ADO's recordset has a CursorType property that will in fact change after an
rs.Open to show the CursorType that was actually achieved.

My problem was that when setting the rowset properties I set *all* of my
properties (DBPROP_SERVERCURSOR, OWNINSERT, OTHERINSERT, OWNUPDATEDELETE,
OTHERUPDATEDELETE, REMOVEDELETED) to be "REQUIRED" (DBPROPOPTIONS_REQUIRED).

The chief culprit which causes the "Multiple-step" error is the
DBPROP_OTHERINSERT property. By making this "REQUIRED" (ie. mandatory) you
are telling OLE DB that you *must* be able to see other's inserts. When SQL
Server discovers that this requested cursor type is incompatible with the
query (due to the ORDER BY clause), it fails.

My solution is that I have instead made DBPROP_OTHERINSERT "OPTIONAL"
(DBPROPOPTIONS_OPTIONAL). Now if SQL Server can provide the Dynamic cursor
it will, but if it can't then rather than failing it ends up downgrading to
a Keyset driven cursor. This is exactly what happens in both ODBC and ADO so
achieving compatible behaviour is actually beneficial.

I hope this helps anyone else having the same problems.

Kevin.

Relevant Pages

  • Re: Setting bind variables or defines from applications?
    ... Reopening cursors? ... we use ODBC (and running ODBC in ... Didn't hear of bind variables. ... returning a REF cursor? ...
    (comp.databases.oracle.misc)
  • Re: date
    ... How to create a cursor object? ... I dont have any VFP database. ... using connection string cnnSinaiRes (ODBC) as follows. ...
    (microsoft.public.fox.vfp.forms)
  • Re: deadlocks when fetching
    ... >> It sounds like you are using a server side cursor. ... recommended in ODBC 3.0 and later. ... But as Andrew mentioned, check your cursor type. ... SQL Server generating a result set and pulling ...
    (microsoft.public.sqlserver.programming)
  • Re: General field?
    ... ODBC automatically convert a Memo field of any type to General. ... >>>I am using a memo field in a VFP8 dbf to hold a bmp file. ... >>> the resulting cursor is a 'General' data type. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)