Microsoft KB Archive/248668

From BetaArchive Wiki
Knowledge Base

Article ID: 248668

Article Last Modified on 9/30/2003


  • Microsoft OLE DB Provider for Oracle Server 1.0
  • Microsoft OLE DB Provider for Oracle Server 1.0

This article was previously published under Q248668


The following error message may appear when 5000 records or more are retrieved, and when each record contains 4 bytes of data:

8007000e Not enough storage is available to complete this operation.

With a client-side cursor (in other words, when an ActiveX Data Objects (ADO) Recordset's CursorLocation property is set to adUseClient), the following error occurs instead:

80004005 Data provider or other service returned an E_FAIL status

Note that the computer is not really out of memory. Microsoft OLE DB Provider for Oracle and its internal algorithm, which attempts to allocate a buffer to hold the rows, fails if the rowset size is 4 bytes or less.


To work around this problem, return recordsets larger than 4 bytes.


Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC 2.6.


Steps to Reproduce the Problem

  1. Create a table in Oracle with the following statement, by using SQL*Plus or some other database utility, and by using the DEMO UserID and DEMO password:

  2. Create a Visual Basic application and put the following code in the Form's Load section (you need to create a DSN named "ORACONN" or modify the code to reflect a DSN you have already created):

    cnn.Open "dsn=ORACONN;uid=demo;pwd=demo", , , -1
      cmd.ActiveConnection = cnn
      For i = 1 To 7000
        cmd.CommandText = "insert into DEMO.TABLE1 (FIELD1) Values(" & Str(i) & ")"
      Next i
  3. Create a Visual Basic form with a list box and a button. In the handler for the button, paste the following code:

    Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rstRecordIDs As ADODB.Recordset
        Dim strCnn As String
        Set cnn = New ADODB.Connection
        Set cmd = New ADODB.Command
       On Error GoTo Err_cmdDBTest_Click
       'Open recordset from table.
       'Connection string #1 using OLEDB provider for oracle fails when attempting to move to
       'rows around 5000.
       strCnn = "Provider=MSDAORA.1;Password=demo;User ID=demo;Data Source=dseoracle8"
       'Connection string #2 using MS ODBC for Oracle works fine
       'strCnn = "DSN=ORACONN;pwd=demo;uid=demo"
       Set rstRecordIDs = New ADODB.Recordset
       rstRecordIDs.CursorType = adOpenForwardOnly
       rstRecordIDs.LockType = adLockReadOnly
       rstRecordIDs.CursorLocation = adUseServer
       rstRecordIDs.MaxRecords = 100000
       rstRecordIDs.CacheSize = 100
       Debug.Print cnn.Version
       rstRecordIDs.Open "SELECT FIELD1 FROM DEMO.TABLE1 ORDER BY FIELD1", strCnn, , , adCmdText
        If rstRecordIDs.EOF Then
          MsgBox "No records!"
          Exit Sub
        End If
       If rstRecordIDs.EOF Then
         MsgBox "No Rows!"
         Exit Sub
       End If
       Do While True
         lstData.AddItem "Record ID: " & rstRecordIDs!FIELD1
         If rstRecordIDs.EOF Then
           MsgBox "At end of recordset!"
           Exit Do
         End If
       Exit Sub
        Debug.Print "Error Description : " + Err.Description
    End Sub"

Keywords: kbbug kbfix kboracle kbprovider kbdatabase kbmdac260fix kbmdacnosweep KB248668