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

View products that this article applies to.

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 "dbcc traceon" 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.

Back to the top

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 "out-of-line columns". 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.

Back to the top

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 (http://support.microsoft.com/kb/290211/EN-US/ ) 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 (http://support.microsoft.com/kb/300635/EN-US/ ) 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

Back to the top

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.

Back to the top

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.

Back to the top

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.

Back to the top

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

#define DBINITCONSTANTS

#include "stdafx.h"

#include <stdio.h>
#include <windows.h>
#include <atlbase.h>
#include <iostream>

#include <comdef.h>
#include <oledb.h>
#include <oledberr.h>
#include <sqloledb.h>


#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"SQLOLEDB", &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("YourServer"));

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

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

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


	//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"Select * from pubs..bigvarchar");
	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("Returned HRESULT:  \t%#X\n", hr);
	printf("Returned values:  m_ID = %i, m_bigVarchar = %s\n", pdata->m_ID, pdata->m_bigVarchar);
	printf("\nHit any key to continue...");
	getchar();


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


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

	pICommandText->Release();


	return 0;
}

Back to the top