Error adding command properties for stored procedure call

andrew_k

unread,

Aug 28, 2009, 9:52:01 PM8/28/09

to

I'm updating an OleDb application. I didn't write the original logic however
it closely resembles the OleDb samples provided by Microsoft. It uses the
standard IDBInitialize, IDBCreateSession, IDBCreateCommand and ICommandText
interfaces to call a stored procedure, returning an IRowset instance. The
ICommandWithParams interface is used to set parameter information for the
stored procedures and IID_IAccessor to create bindings for the buffer to hold
the returned data. ie. fairly standard OleDb code, as noted virtually
identical to the samples provided by Microsoft. It works well and has done
for a while. Currently the Rowsets are all forward only. All the update
requires is that the Rowsets support being able to scroll through the data
more than once. According to the documentation this is done by creating an
instance of the ICommandProperties interface, filling some DBPROP structures
and calling ICommandProperties::SetProperties. However when I do this I get
an error on ICommandText::Execute. If I subsequently call
ICommandProperties::GetProperties with the guidPropertySet of an DBPROPIDSET
struct set to DBPROPSET_PROPERTIESINERROR it tells me that the properties
I've set are invalid. This happened regardless of the properties I attempted
to set. Out of curiosity I created a test application that ran a select
command rather than calling a stored procedure. This ran perfectly. When I
switched the logic so that it called a parameter-less stored procedure that
returned the exact same data I got the errors again, suggesting that there is
something different you need to do for stored procedures. However this is old
technology now and I can't find any information (as well as being something
of an OleDb noob!)

Any help much appreciated.

Erland Sommarskog

unread,

Aug 29, 2009, 8:36:55 AM8/29/09

to

Exactly which properties did you try to set?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

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.mspx

andrew_k

unread,

Aug 29, 2009, 5:35:55 PM8/29/09

to

On Aug 30, 12:36 am, Erland Sommarskog <esq...@sommarskog.se> wrote:

Thanks for your reply. Initially I tried the following:

DBPROP_OWNUPDATEDELETE: VARIANT_FALSE
DBPROP_OWNINSERT: VARIANT_FALSE
DBPROP_OTHERINSERT: VARIANT_FALSE
DBPROP_OTHERUPDATEDELETE: VARIANT_FALSE
DBPROP_IRowsetLocate: VARIANT_TRUE - will set bookmarks as well
DBPROP_CANSCROLLBACKWARDS: VARIANT_TRUE
DBPROP_CANFETCHBACKWARDS: VARIANT_TRUE
DBPROP_QUICKRESTART: VARIANT_TRUE

I started cutting out properties until I just had:
DBPROP_IRowsetLocate: VARIANT_TRUE
DBPROP_QUICKRESTART: VARIANT_TRUE

Then just:
DBPROP_IRowsetLocate: VARIANT_TRUE

But ICommandText::Execute always returned the multiple errors HRESULT.
As soon as I removed the code to set properties everything ran
perfectly once again.

andrew_k

unread,

Aug 29, 2009, 5:48:12 PM8/29/09

to

Just to add, I started attempting to set DBPROPSET_ROWSET properties
because my call to:
pIRowset->RestartPosition(DB_NULL_HCHAPTER)
returned error 0x80040e18 "Rowset position cannot be restarted".
Adding properties results in error 0x80040e21"Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if
available. No work was done." when calling ICommandText::Execute. If I
set the DBPROP_IRowsetLocate property via ICommandText::SetProperties
a subsqequent call to ICommandText::GetProperties with the
guidPropertySet of the DBPROPIDSET struct set to
DBPROPSET_PROPERTIESINERROR returns 0x82: the enum value for
DBPROP_IRowsetLocate.

Erland Sommarskog

unread,

Aug 30, 2009, 11:05:18 AM8/30/09

to

andrew_k (andr...@discussions.microsoft.com) writes:
> I'm updating an OleDb application. I didn't write the original logic
> however it closely resembles the OleDb samples provided by Microsoft. It
> uses the standard IDBInitialize, IDBCreateSession, IDBCreateCommand and
> ICommandText interfaces to call a stored procedure, returning an IRowset
> instance. The ICommandWithParams interface is used to set parameter
> information for the stored procedures and IID_IAccessor to create
> bindings for the buffer to hold the returned data. ie. fairly standard
> OleDb code, as noted virtually identical to the samples provided by
> Microsoft.

Is that the GetFastForwardCursor sample you have in mind?

Anyway, I think can can guess which is the culprit:

dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[0].vValue)) = VARIANT_TRUE;

Running a server-side cursor over a stored procedure can be difficult.

If you have a static client-side rowset, you should not have any problems,
I think.

Message has been deleted

andrew_k

unread,

Aug 30, 2009, 2:57:21 PM8/30/09

to

On Aug 31, 3:05 am, Erland Sommarskog <esq...@sommarskog.se> wrote:

Thanks for your reply, much appreciated. What I am after is something
akin to an ADO disconnected recordset that supports backwards scrolling
(??)

andrew_k

unread,

Sep 10, 2009, 12:31:01 AM9/10/09

to

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 (andrewkl...@gmail.com) 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

unread,

Sep 10, 2009, 4:58:18 PM9/10/09

to

andrew_k (and...@discussions.microsoft.com) writes:
> 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?

I seem to recall that you access this newsgroup through Google. Google
users seem to have difficulties with attachments. And, well, when I
tried to post it from the server I normally read from, I was not
permitted to because it was considered binary. I just posted it through
Microsoft's newsserver and did not think more of it.

I've pasted the code at the end. Beware that comments etc may be wrapped.

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

I read the documentation a little closer, and I get the impression that
this is not a property you add as a consumer, but one that the provider
sets and you can query whether the interface is available, before you
request it.

> 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

Maybe you should post the code of the procedure?

> 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.

When I tried specifying DBPROP_CLIENTCURSOR, I got an error when
setting properties. Looking in the OLE DB docs, I see that you are
normally supposed to set this one, and it is not listed in Books
Online for SQL Server Native Client.

Here is my modified version GetFastForwardCursor that shows that using
RestartLocation reruns the query.

//=====================================================================
//
// File: GetFastForwardCursor.cpp
// Summary: The following example shows how to set the rowset properties to
// obtain a FAST_FORWARD cursor. After the properties are set, a
// SELECT statement is executed to retrieve and display the Name column of the
// Purchasing.Vendor table in the AdventureWorks database.
// Date: December 7, 2005
//
//---------------------------------------------------------------------
//
// This file is part of the Microsoft SQL Server Code Samples.
// Copyright (C) Microsoft Corporation. All rights reserved.
//
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation. See these other
//materials for detailed information regarding Microsoft code samples.
//
//THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
//ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
//THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//
//=====================================================================
#define INITGUID
#define DBINITCONSTANTS
#define OLEDBVER 0x0250 // to include correct interfaces

#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <sqlncli.h>
#include <oledberr.h>

IDBInitialize* pIDBInitialize = NULL;
ICommandText* pICommandText = NULL;

// Connect to the server and create a command object.
int InitializeAndConnect();

// Set the properties to get a FAST_FORWARD cursor.
int SetRowsetProperties();

// This function executes a command and displays the results.
int ExecuteAndDisplay();

// Release memory.
void Cleanup();

int main() {
if (InitializeAndConnect() == -1) {
// Handle error.
printf("Failed to initialize and connect to the server.\n");
return -1;
}

// Set the row properties to FAST_FORWARD cursor.
if (SetRowsetProperties() == -1) {
// Handle error.
printf("Failed to set the rowset properties.\n");
return -1;
}

// Execute a command and display the results.
if (ExecuteAndDisplay() == -1) {
// Handle error.
printf("Failed to execute a command and display the results.\n");
return -1;
}

Cleanup();
}

int InitializeAndConnect() {
HRESULT hr = S_OK;

IDBProperties* pIDBProperties = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;

DBPROPSET dbPropSet;
DBPROP dbProp[4];
int iRetVal = 0;

// Initialize OLE
if ( FAILED( hr = OleInitialize( NULL ) ) ) {
// Handle errors here.
return -1;
}

// Create an instance of Microsoft SQL Server Native Client OLE DB Provider.
if ( FAILED( hr =
CoCreateInstance(CLSID_SQLNCLI10, NULL, CLSCTX_INPROC_SERVER, IID_IDBProperties, (void **) &pIDBProperties ))) {
// Handle errors here.
return -1;
}

// Set up the connection properties.
dbProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;

dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;

V_VT(&(dbProp[0].vValue)) = VT_BSTR;

V_BSTR(&(dbProp[0].vValue)) = SysAllocString( L"(local)" );

dbProp[1].dwPropertyID = DBPROP_AUTH_INTEGRATED;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[1].vValue)) = SysAllocString( L"SSPI" );

dbProp[2].dwPropertyID = DBPROP_NULLCOLLATION;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[2].vValue)) = SysAllocString( L"" );

dbProp[3].dwPropertyID = DBPROP_INIT_CATALOG;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[3].vValue)) = SysAllocString( L"AdventureWorks" );

dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 4;
dbPropSet.guidPropertySet = DBPROPSET_DBINIT;

if ( FAILED( hr = pIDBProperties->SetProperties( 1, &dbPropSet ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

SysFreeString( V_BSTR(&(dbProp[0].vValue)) );
SysFreeString( V_BSTR(&(dbProp[1].vValue)) );
SysFreeString( V_BSTR(&(dbProp[2].vValue)) );
SysFreeString( V_BSTR(&(dbProp[3].vValue)) );

// Get an IDBInitialize interface.
if ( FAILED( hr =
pIDBProperties->QueryInterface( IID_IDBInitialize, (void **) &pIDBInitialize ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

// Call Initialize.
if ( FAILED( hr = pIDBInitialize->Initialize())) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

// Get a IDBCreateSession interface.
if ( FAILED( hr =
pIDBInitialize->QueryInterface( IID_IDBCreateSession, (void **) &pIDBCreateSession ))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

// Create a session
if ( FAILED( hr =
pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, (IUnknown **) &pIDBCreateCommand))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

// Create a command.
if ( FAILED( hr =
pIDBCreateCommand->CreateCommand( NULL, IID_ICommandText, (IUnknown **) &pICommandText))) {
// Handle errors here.
iRetVal = -1;
goto CLEANUP;
}

CLEANUP:
// Release all the objects not needed anymore.
pIDBProperties->Release();
if ( pIDBCreateSession )
pIDBCreateSession->Release();
if ( pIDBCreateCommand )
pIDBCreateCommand->Release();

return iRetVal;
}

int SetRowsetProperties() {
HRESULT hr = S_OK;
ICommandProperties* pICommandProperties = NULL;
DBPROPSET dbPropSet;
DBPROP dbProp[5];
int iRetVal = 0;

// Get an ICommandProperties object.
if ( FAILED( hr =
pICommandText->QueryInterface( IID_ICommandProperties, (void **) &pICommandProperties ))) {
// Handle errors here.
return -1;
}

// Set up the properties to get a FAST_FORWARD cursor.

dbProp[0].dwPropertyID = DBPROP_SERVERCURSOR;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[0].vValue)) = VARIANT_TRUE;

dbProp[1].dwPropertyID = DBPROP_OTHERINSERT;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[1].vValue)) = VARIANT_TRUE;

dbProp[2].dwPropertyID = DBPROP_OTHERUPDATEDELETE;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[2].vValue)) = VARIANT_TRUE;

dbProp[3].dwPropertyID = DBPROP_OWNINSERT;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[3].vValue)) = VARIANT_TRUE;

dbProp[4].dwPropertyID = DBPROP_OWNUPDATEDELETE;
dbProp[4].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[4].colid = DB_NULLID;
V_VT(&(dbProp[4].vValue)) = VT_BOOL;
V_BOOL(&(dbProp[4].vValue)) = VARIANT_TRUE;

dbPropSet.rgProperties = dbProp;
dbPropSet.cProperties = 1;
dbPropSet.guidPropertySet = DBPROPSET_ROWSET;

if ( FAILED( hr = pICommandProperties->SetProperties( 1, &dbPropSet))) {
// Handle errors here.
iRetVal = -1;
}

// Release the ICommandProperties object.
pICommandProperties->Release();

return iRetVal;
}

int ExecuteAndDisplay() {
HRESULT hr = S_OK;
IRowset* pIRowset = NULL;
IAccessor* pIAccessor = NULL;

BYTE* pData = NULL;
DBCOUNTITEM cRowsObtained = 0;
ULONG cCount = 0;

HROW* pRows = new HROW[10];
HACCESSOR hAccessor = 0;
DBBINDING Bind[1];
int iRetVal = 0;

if (!pRows)
return -1;

// Set the command text.
if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"{call get_vendor_names}")))
// if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"select Name from Purchasing.Vendor")))
// Handle errors and free the memory here.
return -1;

// Execute the command.
if ( FAILED( hr = pICommandText->Execute( NULL, IID_IRowset, NULL, NULL, (IUnknown **) &pIRowset )))
// Handle errors and free the memory here.
return -1;

// Set up the binding structure for Name (nvarchar(50)).
Bind[0].dwPart = DBPART_VALUE;
Bind[0].eParamIO = DBPARAMIO_NOTPARAM;
Bind[0].iOrdinal = 1;
Bind[0].pTypeInfo = NULL;
Bind[0].pObject = NULL;
Bind[0].pBindExt = NULL;
Bind[0].dwFlags = 0;
Bind[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bind[0].obLength = 0;
Bind[0].obStatus = 0;
Bind[0].obValue = 0;
Bind[0].cbMaxLen = 102;
Bind[0].wType = DBTYPE_WSTR;
Bind[0].bPrecision = 0;
Bind[0].bScale = 0;

// Get an IAccessor interface.
if ( FAILED( hr = pIRowset->QueryInterface( IID_IAccessor, (void **) &pIAccessor))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}

// Create an accessor.
if ( FAILED( hr =
pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, Bind, 0, &hAccessor, NULL))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}

// Allocate memory for the data.
pData = new BYTE[102];
if (!(pData /* = new BYTE[102] */ )) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}

// Loop through all of the rows.
int alfine = 0;
DaCapo:
if (alfine++ == 2) goto Coda;
for ( ; ; ) {
if (FAILED( hr = pIRowset->GetNextRows( NULL, 0, 10, &cRowsObtained, &pRows))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}

// Make sure some rows were obtained.
if (cRowsObtained == 0)
break;

// Get the data for the each of the rows.
for ( cCount = 0 ; cCount < cRowsObtained ; cCount++ ) {
// Get the row data needed.
if ( FAILED( hr = pIRowset->GetData( pRows[cCount], hAccessor, pData ))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}

// Display row data.
printf( "%S\n", pData);
}

// Release the rows.
if ( FAILED( hr = pIRowset->ReleaseRows(cRowsObtained, pRows, NULL, NULL, NULL ))) {
// Handle errors and free the memory here.
iRetVal = -1;
goto CLEANUP;
}
}
printf( "/////////////////////////////////////////////\n");
hr = pIRowset->RestartPosition(NULL);

goto DaCapo;
Coda:

CLEANUP:
// Release memory allocated for the data.
delete [] pRows;
delete [] pData;

// Release the HACCESSOR.
if (pIAccessor) {
pIAccessor->ReleaseAccessor( hAccessor, NULL );

// Release the IAccessor object.
pIAccessor->Release();
}

// Release the rowset.
pIRowset->Release();

return iRetVal;
}

void Cleanup() {
HRESULT hr = S_OK;

// Release the ICommandText object.
pICommandText->Release();

// Uninitialize the IDBInitialize object.
if ( FAILED( hr = pIDBInitialize->Uninitialize())) {
// Handle errors here.
}

// Release the IDBInitialize object.
pIDBInitialize->Release();

// Uninitialize OLE.
OleUninitialize();
}

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

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.mspx

andrew_k

unread,

Sep 10, 2009, 6:03:02 PM9/10/09

to

Thanks for your reply. I really appreciate all the trouble you have gone to
helping me out here. I see you are using the SQL Native Client for SQL Server
2005. I'm running SQL Server 2000 here. When I run your code it returned an
error in the call to pICommandText->Execute. Under Profiler I can see it's
trying to open a cursor (exec sp_cursoropen). If I cut and paste the call
from Profiler into Query Analyser I get:

Server: Msg 16937, Level 16, State 1, Procedure get_names, 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

The cursor was not declared.

The stored procedure called is a test one that simply SELECTs the name field
from a table. ie.

SELECT name FROM TableName

There are no parameters involved.

Out of curiosity I wrote some ADO code that called the same stored procedure
using an adOpenStatic cursor and looped through the resulting recordset a
couple of times. The call under Profiler was a simple 'exec', not 'exec
sp_cursoropen'. ie. it didn't attempt to create a cursor. The call only
occurred once under Profiler despite looping through the recordset multiple
times. I need to replicate this behaviour using OleDb. If I remove all
properties from the command object though I get a forward only rowset.

Erland Sommarskog

unread,

Sep 11, 2009, 5:28:54 PM9/11/09

to

andrew_k (and...@discussions.microsoft.com) writes:
> Thanks for your reply. I really appreciate all the trouble you have gone
> to helping me out here. I see you are using the SQL Native Client for
> SQL Server 2005. I'm running SQL Server 2000 here. When I run your code
> it returned an error in the call to pICommandText->Execute. Under
> Profiler I can see it's trying to open a cursor (exec sp_cursoropen). If
> I cut and paste the call from Profiler into Query Analyser I get:
>
> Server: Msg 16937, Level 16, State 1, Procedure get_names, 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
> The cursor was not declared.
>
> The stored procedure called is a test one that simply SELECTs the name
> field from a table. ie.
>
> SELECT name FROM TableName

And that is really a table? In the local database? By chance, it does not
happen to be a view including a linked-server table?

Try this. In tempdb, create this procedure:

CREATE PROCEDURE get_names AS
SELECT name FROM sysobjects WHERE type = 'P'

The try the code below. It's the same file, but I've changed it to use
SQLOLEDB. Also, I've changed DBPROP_SERVERCURSOR to VARIANT_FALSE. You
will find when you run it, that it now runs an EXEC statement. Still
twice though.

Also, could you post the output from "SELECT @@version"?

Erland Sommarskog

unread,

Sep 11, 2009, 5:47:15 PM9/11/09

to

Oops, forgot the code:

Cleanup();
}

CLSID clsid_sqloledb;
CLSIDFromProgID(L"SQLOLEDB", &clsid_sqloledb);

if ( FAILED( hr =

CoCreateInstance(clsid_sqloledb, NULL, CLSCTX_INPROC_SERVER, IID_IDBProperties, (void **) &pIDBProperties ))) {

// Handle errors here.
return -1;
}

// Set up the connection properties.
dbProp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbProp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[0].colid = DB_NULLID;
V_VT(&(dbProp[0].vValue)) = VT_BSTR;

V_BSTR(&(dbProp[0].vValue)) = SysAllocString( L"SHILOH" );

dbProp[1].dwPropertyID = DBPROP_AUTH_INTEGRATED;
dbProp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[1].colid = DB_NULLID;
V_VT(&(dbProp[1].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[1].vValue)) = SysAllocString( L"SSPI" );

dbProp[2].dwPropertyID = DBPROP_NULLCOLLATION;
dbProp[2].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[2].colid = DB_NULLID;
V_VT(&(dbProp[2].vValue)) = VT_BSTR;
V_BSTR(&(dbProp[2].vValue)) = SysAllocString( L"" );

dbProp[3].dwPropertyID = DBPROP_INIT_CATALOG;
dbProp[3].dwOptions = DBPROPOPTIONS_REQUIRED;
dbProp[3].colid = DB_NULLID;
V_VT(&(dbProp[3].vValue)) = VT_BSTR;

V_BSTR(&(dbProp[3].vValue)) = SysAllocString( L"tempdb" );

return iRetVal;
}

V_BOOL(&(dbProp[0].vValue)) = VARIANT_FALSE;

return iRetVal;
}

BYTE* pData = NULL;

DBCOUNTITEM cRowsObtained = 0;
ULONG cCount = 0;

HROW* pRows = new HROW[10];
HACCESSOR hAccessor = 0;
DBBINDING Bind[1];
int iRetVal = 0;

if (!pRows)
return -1;

// Set the command text.
if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"{call get_names}")))

// if ( FAILED( hr = pICommandText->SetCommandText( DBGUID_SQL, L"select Name from Purchasing.Vendor")))
// Handle errors and free the memory here.
return -1;

// Execute the command.
if ( FAILED( hr = pICommandText->Execute( NULL, IID_IRowset, NULL, NULL, (IUnknown **) &pIRowset )))

// Handle errors and free the memory here.
return -1;

// Set up the binding structure for Name (nvarchar(50)).

Bind[0].dwPart = DBPART_VALUE;
Bind[0].eParamIO = DBPARAMIO_NOTPARAM;
Bind[0].iOrdinal = 1;
Bind[0].pTypeInfo = NULL;
Bind[0].pObject = NULL;
Bind[0].pBindExt = NULL;
Bind[0].dwFlags = 0;
Bind[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bind[0].obLength = 0;
Bind[0].obStatus = 0;
Bind[0].obValue = 0;
Bind[0].cbMaxLen = 102;
Bind[0].wType = DBTYPE_WSTR;
Bind[0].bPrecision = 0;
Bind[0].bScale = 0;

// Get an IAccessor interface.
if ( FAILED( hr = pIRowset->QueryInterface( IID_IAccessor, (void **) &pIAccessor))) {