PRB: ATL Consumer Receives DB_S_ERRORSOCCURRED on a Move Operation with SQL Server Text Datatypes (238080)

SYMPTOMS

When accessing Text data from a SQL Server Table by using Microsoft OLE DB Provider for ODBC Drivers, an ATL Consumer receives the following HRESULT after a Move operation:

 HRESULT:   0x00040EDAL ( DB_S_ERRORSOCCURRED )

CAUSE

The SQL Server Text field is a long binary field. The Microsoft SQL Server ODBC driver performs a SQLGetData operation to retrieve long binary or blob data retrieval. The Microsoft SQL Server ODBC driver performs an SQLFetch operation on all the bound columns and then performs the SQLGetData operation on the Text fields. The driver supports retrieving data by using SQLGetData for unbound columns only; that is, the driver returns SQL_GD_BOUND for its SQL_GETDATA_EXTENSIONS attribute. The driver cannot retrieve data from a column if the number of the column specified in the current call is less than the number of the column specified in the preceding call. Therefore, if the Text fields are not the last columns in the table, a call to SQLGetData results in the following error message:

DIAG [S1002] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index (0)

As a result, the HRESULT is returned as 0x00040EDAL ( DB_S_ERRORSOCCURRED ).

RESOLUTION

Use one of the following two workarounds:

Use Microsoft OLEDB Provider for SQL Server instead of the SQL Server ODBC driver through Microsoft OLEDB Provider for ODBC Drivers. -or-
Place the Text fields at the end of the SQL query.

MORE INFORMATION

Steps to Reproduce Behavior

Create a Table, for example, TextTable, in a SQL Server database with one or more Text fields and with one or more non-Text fields after them.
Create an MFC Dialog Application.
Insert an ATL Consumer object, for example, CTextTable, for the SQL Server Table using the Microsoft OLE DB Provider for ODBC Drivers.
Insert a button, for example, Button, in the dialog box.
Using the class wizard, insert an event handler, for example, OnButton1, to handle pressing of the Button1.

Inside the OnButton1 event handler, insert the following code:

HRESULT hr;
CTextTable varTextTable;
hr = varTextTable.Open();
hr = varTextTable.MoveFirst();
varTextTable.Close();

Insert a breakpoint on line 4.
Compile and run the application in debug mode.

Note that after running line 4, the HRESULT returned is 0x00040EDAL ( DB_S_ERRORSOCCURRED ). The status value returned for each of the Text fields is DBSTATUS_E_UNAVAILABLE.