Microsoft KB Archive/248668

Knowledge Base

FIX: "Not Enough Storage Is Available to Complete This Operation" with Oracle OLE DB Provider

Article ID: 248668

Article Last Modified on 9/30/2003

---

APPLIES TO

• 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

SYMPTOMS

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.

RESOLUTION

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

STATUS

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.

MORE INFORMATION

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:

     CREATE TABLE TABLE1 (FIELD1 NUMERIC (4,0))
                           

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) & ")"
       cmd.Execute
     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
      
      rstRecordIDs.MoveFirst
      If rstRecordIDs.EOF Then
        MsgBox "No Rows!"
        Exit Sub
      End If
      
      Do While True
        lstData.AddItem "Record ID: " & rstRecordIDs!FIELD1
        rstRecordIDs.MoveNext
        
        If rstRecordIDs.EOF Then
          MsgBox "At end of recordset!"
          Exit Do
        End If
      Loop
      
      rstRecordIDs.Close
      Exit Sub
   Err_cmdDBTest_Click:
       Debug.Print "Error Description : " + Err.Description
   End Sub"
                           

Keywords: kbbug kbfix kboracle kbprovider kbdatabase kbmdac260fix kbmdacnosweep KB248668

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.