Re: Error adding command properties for stored procedure call

Thanks for your reply. I've had to work on other things for the past week
hence haven't had a change to get back to this. I wasn't able to access your
source code? Has this been removed?

If I add the DBPROP_IRowsetLocate property and no others I get the following
error:

HResult: 0x80040e21
Description: Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.
Source: Microsoft OLE DB Provider for SQL Server

If I run this under SQL Server Profiler I can see that when I add any
properties to the command the execute call attempts to create a cursor. If I
copy the call from Profiler into Query Analyser and run it I get:

Server: Msg 16937, Level 16, State 1, Procedure Tex_TEST4, Line 1
A server cursor is not allowed on a remote stored procedure or stored
procedure with more than one SELECT statement. Use a default result set or
client cursor.
Server: Msg 16945, Level 16, State 1, Procedure sp_cursoropen, Line 9

If I add the DBPROP_CLIENTCURSOR property it makes no difference. ie. same
result (same if I add DBPROP_SERVERCURSOR). The stored procedure in question
performs a simple select on the basis of 2 input parameters. Switching to a
stored procedure with no parameters makes no difference.



"Erland Sommarskog" wrote:

andrew_k (andrewklinkhamer@xxxxxxxxx) writes:
Thanks for your reply, much appreciated. It's a static client side
rowset that I need, but one that lets me call
IRowset::RestartPosition. Basically something akin to an ADO
adOpenStatic cursor. I have the MS OleDb 2.0 reference but have to
admit I'm struggling to know exactly what to do. How do I create a
static client side rowset that lets me call IRowset::RestartPosition
when calling a stored procedure?

I took the GetFastForwardCursor sample, and I created a stored procedure:

create procedure get_vendor_names as
select Name from Purchasing.Vendor

Then I changed the command text to:

L"{call get_vendor_names}"

At the end of ExecuteAndDisplay I added call to IRowset::RestartPosition
and a goto to execute the for loop twice. This code worked and the result
was displayed twice.

However, the call to RestartPosition did not return S_OK, but
DB_S_COMMANDREEXECUTED, and Profiler also confirmed that this was the
case. Furthermore, this also happened when the command text was the
original statement.

My conclusion of this is that you probably don't want
IRowset::RestartPosition, but you want to look at the IRowsetLocate
or IRowsetScroll interfaces. To use them, you probably have to set the
corresponding command properties.

As for how to get a static client-side cursor, I think you should
just rip out the properties you set now. They give you a server-side
cursor, and that is not what you want.

I attach my slightly modified version of the GetFastForwardCursor
sample, in case you've been looking at another sample, or an older
version of it.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books
.

Relevant Pages
Loading