PRB: 80020009 Error When Retrieving Data from SQLID: Q175239
|
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'
The following condition may cause the error to occur:
Text/Blob fields are selected in an order preceding other types of fields.
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 "<Your DSN for Pubs database>"
'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> This behavior is by design.
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. Since 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 since 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: kbdsi
Keywords : kberrmsg kbADO kbVisID kbOBDC
Version : WINDOWS:1.0; winnt:
Platform : WINDOWS winnt
Issue type : kbprb Last Reviewed: March 4, 1999