Microsoft KB Archive/303446

= FIX: SQLOLEDB Returns No Data When Using AutoFetch Option with Fast Forward Cursors =

Article ID: 303446

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q303446



SYMPTOMS
The SQL Server native provider (SQLOLEDB) may return no data for a VARCHAR field that is larger than 255 characters under the following conditions:
 * The properties for the rowset will create a Fast Forward cursor.
 * The AutoFetch property (a provider-specific property) has been set to TRUE.
 * The query contains a VARCHAR field greater than 255 characters in size.

When data is fetched for the row, IRowset->GetData returns DB_S_ERRORSOCCURED (0x800040eda) and the status of the column is DBSTATUS_E_UNAVAILABLE. Generating a low-level trace through NetMon or &quot;dbcc traceon&quot; commands will show that the data was correctly returned to the client. If the VARCHAR column is 255 characters or less, or if the cursor type is something other than Fast Forward, data will be present in the rowset as expected.

This problem occurs in all versions of the SQL Server native provider versions 2000.80.380.0 (SQL 2000/MDAC 2.6 SP1 release) and earlier.



CAUSE
When a rowset is being returned from a query, the provider builds an in-memory structure that is laid out according to the metadata sent back by SQL Server. Normally, the provider attempts to keep the data in a contiguous structure to make it easier to manage and simplify binding.

When VARCHAR columns are larger than 255 characters, the SQL Server provider uses what is called &quot;out-of-line columns&quot;. This means that the data for the column is not kept in-line with other data. Instead, a separate buffer is allocated for the data, and the column information points to the new buffer.

Due to a design flaw in the provider when using the AutoFetch option with Fast Forward cursors, the buffers for the out-of-line columns have not yet been allocated at the time data is being fetched. This leads the provider to believe there is no data for the column, and no data is returned for that column in the rowset.



RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000, the latest service pack for MDAC 2.6, or the hotfix referenced below. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

Hotfix
The English version of this fix should have the following file attributes or later.

MDAC 2.6 Date         Version         Size            File name

07/12/2001   2000.80.307.0   491,584 bytes   Sqloledb.dll 07/12/2001   2000.80.307.0   61,440 bytes    Sqloledb.rll 01/08/2001                    1,652 bytes    EULA.txt MDAC 2.6 SP1 Date         Version         Size            File name --- 08/15/2001   2000.80.434.0   495,697 bytes   Sqloledb.dll 08/15/2001   2000.80.434.0    61,440 bytes   Sqloledb.rll 01/08/2001                     1,652 bytes   EULA.txt MDAC 2.7 Date         Version         Size            File name --- 06/11/2002   2000.81.9001.4  450,560 bytes   Sqloledb.dll



WORKAROUND
You can work around the problem in the following ways:
 * Change the cursor type to something other than Fast Forward.
 * Do not set the AutoFetch property on the rowset.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.



MORE INFORMATION
SQL Server 2000 offers the AutoFetch property and Fast Forward cursors as a fast, lightweight means of returning a small resultset to the client with a minimal amount of network traffic. Fast Forward cursors are optimized forward-only, read-only cursors. When the AutoFetch option is also set, SQL Server automatically returns the first row or batch of rows to the client and closes the cursor when the end of the resultset is reached. These optimizations eliminate the network round-trips normally required to request the resultset and close the cursor.

Fast Forward cursors and the AutoFetch option are both documented in the SQL Server 2000 Books Online.

Steps to Reproduce Behavior
  Copy the sample code below into a new Microsoft Visual C++ console application and compile the code. You may need to change the data source name, user id, and password, so that these values refer to valid values for your SQL Server. This code assumes that a table has already been created in the Pubs database using the following syntax: create table BigVarchar(ID int, BigVar varchar(1000)) insert into BigVarchar values(1, 'A123')  Run the code, and notice that the m_bigVarchar variable is empty, although the m_ID variable is not.

Sample Code

 * 1) define DBINITCONSTANTS


 * 1) include &quot;stdafx.h&quot;


 * 1) include <stdio.h>
 * 2) include <windows.h>
 * 3) include <atlbase.h>
 * 4) include


 * 1) include <comdef.h>
 * 2) include 
 * 3) include 
 * 4) include <sqloledb.h>


 * 1) define num_rowProps 24

struct PData {   //status and value int m_ID_Status; int m_ID; int m_bigVarchar_Status; char m_bigVarchar[1001]; };

int main(int argc, char* argv[]) {

HRESULT        hr = NULL; IUnknown *     pIUnknown = NULL; IUnknown*      pISession = NULL; IDBInitialize *    pIDBInitialize = NULL; IDBProperties*     pIDBProperties = NULL; IDBCreateSession*  pIDBCreateSession = NULL; IDBCreateCommand*  pIDBCreateCommand = NULL; ICommandText*      pICommandText = NULL; ICommandProperties* pICommandProperties; IRowset*       pIRowset = NULL; DBPROPSET      dbPropSet; DBPROP         rowProp[num_rowProps]; IAccessor*     pIAccessor = NULL; HACCESSOR      hAccessor; DBBINDING*     rgBindings; DBBINDSTATUS*      rgStatus; rgBindings     = new DBBINDING[2];

CLSID          clsid; ULONG          i;

// variables for fetching data ULONG              cRows; HROW               hRows[1]; HROW*              rghRows = &hRows[0]; LONG               cRowsAffected;

hr = CoInitialize(NULL);

if( FAILED(hr = CLSIDFromProgID(L&quot;SQLOLEDB&quot;, &clsid) ) ) return 0;

hr = CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER,       IID_IDBInitialize, (void**)&pIDBInitialize);

const ULONG nProps = 5; DBPROP InitProperties[nProps]; DBPROPSET rgInitPropSet[1];

for (i = 0; i < nProps; i++) {       VariantInit(&InitProperties[i].vValue); InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED; InitProperties[i].colid = DB_NULLID; }

//level of prompting that will be done to complete the //connection process InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT; InitProperties[0].vValue.vt = VT_I2; InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;

//datasource name InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE; InitProperties[1].vValue.vt = VT_BSTR; InitProperties[1].vValue.bstrVal = SysAllocString(OLESTR(&quot;YourServer&quot;));

//userid InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID; InitProperties[2].vValue.vt = VT_BSTR; InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(&quot;sa&quot;));

//password InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD; InitProperties[3].vValue.vt = VT_BSTR; InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(&quot;&quot;));

//database InitProperties[4].dwPropertyID = DBPROP_INIT_CATALOG; InitProperties[4].vValue.vt = VT_BSTR; InitProperties[4].vValue.bstrVal = SysAllocString(OLESTR(&quot;Pubs&quot;));

//assign the property structures to the property set rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT; rgInitPropSet[0].cProperties = nProps; rgInitPropSet[0].rgProperties = InitProperties;

//set the properties hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties); hr = pIDBProperties->SetProperties(1, rgInitPropSet); pIDBProperties->Release;

hr = pIDBInitialize->Initialize; if (FAILED(hr)) return 0;

// create a session and a command, set the command text hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&pIDBCreateSession); hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**)&pIDBCreateCommand);

pIDBCreateSession->Release;

hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**)&pICommandText); hr = pICommandText->SetCommandText(DBGUID_DBSQL, L&quot;Select * from pubs..bigvarchar&quot;); hr = pICommandText->QueryInterface(IID_ICommandProperties, (void **)&pICommandProperties);

// set the command/rowset properties for a Fast Forward cursor for (i = 0; i < num_rowProps; i++) {       VariantInit(&rowProp[i].vValue); rowProp[i].dwOptions = DBPROPOPTIONS_REQUIRED; rowProp[i].colid = DB_NULLID; }

static struct {   DBPROPID    m_PropId; BOOL   m_Val; }       crsProps[num_rowProps] = {           {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_IMMOBILEROWS,      false}, {DBPROP_IMultipleResults,  false}, {DBPROP_ABORTPRESERVE,     true}, {DBPROP_COMMITPRESERVE,    true} };

for (i = 0; i < num_rowProps; i++ ) {   VariantInit(&rowProp[i].vValue); rowProp[i].dwOptions = DBPROPOPTIONS_REQUIRED; rowProp[i].colid = DB_NULLID; rowProp[i].dwPropertyID = crsProps[i].m_PropId; rowProp[i].vValue.vt = VT_BOOL; rowProp[i].vValue.boolVal = crsProps[i].m_Val ? VARIANT_TRUE : VARIANT_FALSE; }

dbPropSet.rgProperties      = rowProp; dbPropSet.cProperties       = num_rowProps; dbPropSet.guidPropertySet   = DBPROPSET_ROWSET; hr = pICommandProperties->SetProperties(1, &dbPropSet); pICommandProperties->Release; pICommandProperties = NULL;

// set the provider-specific AutoFetch property DBPROP           rowProp2[1]; DBPROPSET        rgPropSet2; rgPropSet2.guidPropertySet = DBPROPSET_SQLSERVERROWSET; rgPropSet2.cProperties = 1; rgPropSet2.rgProperties = rowProp2;

VariantInit(&rowProp2[0].vValue); rowProp2[0].dwOptions = DBPROPOPTIONS_REQUIRED; rowProp2[0].colid = DB_NULLID; rowProp2[0].dwPropertyID = SSPROP_CURSORAUTOFETCH; rowProp2[0].vValue.vt = VT_BOOL; rowProp2[0].vValue.boolVal = VARIANT_TRUE;

hr = pICommandText->QueryInterface(IID_ICommandProperties, (void **)&pICommandProperties); hr = pICommandProperties->SetProperties(1, &rgPropSet2); pICommandProperties->Release;

// set up the binding structures for fetching data rgBindings = new DBBINDING[2]; rgStatus = new DBBINDSTATUS[2];

PData * pdata = new PData; memset( pdata, 0, sizeof(PData) ); // binding for ID field rgBindings[0].iOrdinal = 1; rgBindings[0].pTypeInfo = NULL; rgBindings[0].pObject = NULL; rgBindings[0].pBindExt = NULL; rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED; rgBindings[0].eParamIO = DBPARAMIO_OUTPUT; rgBindings[0].dwFlags = 0;

rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS; rgBindings[0].obStatus = offsetof(PData, m_ID_Status); rgBindings[0].obLength = 0; rgBindings[0].obValue = offsetof(PData, m_ID); rgBindings[0].cbMaxLen = 4; rgBindings[0].wType = DBTYPE_I8; rgBindings[0].bPrecision = 0; rgBindings[0].bScale = 0;

// binding for BigVar field rgBindings[1].iOrdinal = 2; rgBindings[1].pTypeInfo = NULL; rgBindings[1].pObject = NULL; rgBindings[1].pBindExt = NULL; rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED; rgBindings[1].eParamIO = DBPARAMIO_OUTPUT; rgBindings[1].dwFlags = 0;

rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS; rgBindings[1].obStatus = offsetof(PData, m_bigVarchar_Status); rgBindings[1].obLength = 0; rgBindings[1].obValue = offsetof(PData, m_bigVarchar); rgBindings[1].cbMaxLen = 1001; rgBindings[1].wType = DBTYPE_STR; rgBindings[1].bPrecision = 0; rgBindings[1].bScale = 0;

// execute the command and create an Accessor hr = pICommandText->Execute(NULL,                                   IID_IRowset,                                    NULL,                                    &cRowsAffected,                                    (IUnknown**)&pIRowset);

hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor); hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,                                   2,                                    rgBindings,                                    sizeof(PData),                                    &hAccessor,                                    rgStatus);

// fetch the data hr = pIRowset->GetNextRows(NULL, 0, 1, &cRows, &rghRows); hr = pIRowset->GetData(rghRows[0], hAccessor, (void*)pdata);

printf(&quot;Returned HRESULT: \t%#X\n&quot;, hr); printf(&quot;Returned values: m_ID = %i, m_bigVarchar = %s\n&quot;, pdata->m_ID, pdata->m_bigVarchar); printf(&quot;\nHit any key to continue...&quot;); getchar;

hr = pIRowset->ReleaseRows(1, hRows, NULL, NULL, NULL);

pIRowset->Release; pIAccessor->ReleaseAccessor(hAccessor,NULL); pIAccessor->Release;

pICommandText->Release;

return 0; }

Additional query words: sqloledb sql server native provider autofetch fast forward cursor cursors empty blank no data

Keywords: kbbug kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000presp2fix kbmdac260fix kbsqlserv2000sp3fix kbmdac260sp2fix kbhotfixserver KB303446

-

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

© Microsoft Corporation. All rights reserved.