Re: Error adding command properties for stored procedure call

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" wrote:

andrew_k (andrewk@xxxxxxxxxxxxxxxxxxxxxxxxx) 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;

.

Relevant Pages