PRB: 80020009 Error When Retrieving Data from SQL

ID: Q175239

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2
Active Server Pages
Microsoft Visual InterDev, versions 1.0, 6.0

SYMPTOMS

The following error occurs when accessing a recordset in an Active Server Pages (ASP) file that contains "Text" or "Blob" type data from a SQL table:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

CAUSE

The following condition may cause the error to occur:

Text/Blob fields are selected in an order preceding other types of fields.

RESOLUTION

When dealing with BLOB fields from SQL Server, you need to put them to the right of non-BLOB columns in the resultset. To be safe, you should also read the columns in left-to-right order, so if you have two BLOB columns as the last two columns in your resultset, read the first one and then the second. Do not read them in the reverse order.

To demonstrate the correct order of field selection create a new ASP page in a Visual InterDev Project and paste the following code in the blank ASP page:


   <%@ Language=VBScript %>
   <HTML>
   <BODY bgcolor=white>
   <%
      Set cn = Server.CreateObject("ADODB.Connection")
      Set rs = Server.CreateObject("ADODB.Recordset")

      'Open the connection.
      cn.Open "dsn=yoursystemdsn;uid=sa;pwd=;database=pubs;"

      'Open the recordset.

      'Notice that the Blob field, pr_info, is last in the field order.

      rs.Open "select pub_id, pr_info from pub_info", cn

      While Not rs.EOF

         Response.Write "<P>PR Info:<P>" & rs("pr_info")
         Response.Write "<P>That was the PR Info for PubID " & 
                          rs("pub_id")
         rs.MoveNext
      Wend
   %>
   </BODY>
   </HTML>

STATUS

This behavior is by design. However, it does not seem to occur when using Mdac 2.1sp2 with the 3.7 driver for SQL Server and Microsoft Visual Interdev 6.0 sp3.

MORE INFORMATION

SQL Server is sending back the data on the wire and the client is essentially receiving a stream of bits read sequentially on the network wire. With bound columns (that is, the values can be copied into local memory buffers and cached there), the driver transfers data in those columns to memory buffers. Once the data is in local buffers, you may read the data in any order. Therefore, you can read result columns in any order when all the columns are bound (not BLOBs).

When you include BLOB columns, the length of the column can be approximately 2 gigabytes and data access libraries typically do not bind those columns since the driver often cannot determine exactly how large the BLOB is until retrieved. Also, data access libraries typically avoid caching BLOB data since this may consume large amounts of memory and caching it both in the data access library and your application is inefficient. If the data access driver is requested to return the contents of a BLOB column, it typically discards non-bound columns that precede the requested BLOB column, since it must retrieve the sequential data stream before it can read the requested column. Therefore, it is more efficient to read your resultset from left-to-right since that matches the way the data is retrieved.

Note that this describes the behavior of SQL Server. Oracle and other client/server DBMSs may do the same thing, but it is not required.

Perhaps a better alternative is to avoid using a Text column. Because SQL Server allocates space in 2K chunks, using Text columns may result in inefficient use of storage if the text length is very small. Backup time is also affected because it takes longer to dump the transaction log. It is often better to create another table that has the PK of your existing table, a chunk number column, and a varchar (255) column. Divide the text into as many 255 character chunks needed and insert as many rows in the new table as there are chunks. It is usually worth the additional coding time since you make more efficient use of storage and backups go much faster.

Additional query words:

Keywords : kbDatabase kbGrpVB kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2,6.0; winnt:
Platform : WINDOWS winnt
Issue type : kbprb