PRB: 80020009 Error When Retrieving Data from SQL

Last reviewed: October 20, 1997
Article ID: Q175239

The information in this article applies to:

ActiveX Data Objects (ADO), version 1.0
Microsoft Active Server Pages, version 1.0
Microsoft Visual InterDev, version 1.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.

The code example below demonstrates the correct order of field selection:

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

      'open the connection
      cn.Open "<Put your DSN here>"

      '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.

MORE INFORMATION

The reason for all of this is easily understood when you think about how SQL Server is sending back the data on the wire. Your client is essentially getting a stream of bits back and can read them sequentially off of the network wire. When you have columns that can be bound (that is, the values can be copied into local memory buffers and cached there), the driver can transfer data in those columns into memory buffers pretty efficiently. Once they are there, you can read them in any order you want. This is why you can read result columns in any order when all the columns are bound (not BLOBs).

However, when you include BLOB columns, the length of the column can be huge and data access libraries will typically not bind those kinds of columns since the driver will often not be able to tell exactly how big the BLOB is until it's all read off the wire. Libraries will also typically avoid caching the BLOB data since it can consume lots of memory and caching it both in the library and your application is a waste.

So if you ask the driver to return the contents of a BLOB column, it will typically throw away any non-bound columns that preceded the requested BLOB column, since it has to read that data off the wire before it can start reading the column you asked for. Therefore, it's always best to read from left-to-right in your result set since that matches the way the data is laid out on the wire.

Note that this is behavior of SQL Server. Oracle and other client/server DBMSs will likely do the same thing, but it's not guaranteed.

Of course, the best thing of all is not to use a Text column. Doing so causes the server to allocate column space in 2K chunks and that's a lot of space to store something that may only be a few words (it also takes more time to backup and dump the transaction log too). It's often better to create another table that has the PK of your existing table, a chunk number column, and a varchar(255) column. You break the text into as many 255 character chunks as is needed and insert as many rows in the new table as there are chunks. If there is no text at all, don't insert any rows. It's a bit more work (not much though) and you can save a lot of room, make your backups go much faster and avoid lots of headaches that are inherently caused by working with BLOB columns.

Keywords          : kberrmsg VIADO VIODBC
Technology        : kbInetDev
Version           : WINDOWS:1.0; WINNT:1.0
Platform          : WINDOWS winnt
Issue type        : kbprb

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.