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

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))
                        
```

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

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

Microsoft KB Archive/248668

Contents