Professional Web Applications Themes

Provider error '8007000e' - Not enough storage ... - ASP

Hi, I have an asp page that uses ADO to send a query to an sql server and puts the result data in a recordset. Then I use: -- variable = result_recordset.getrows(number_of_rows) -- to put the result data in a variable, but in that line sometimes I receive the error message: -- Provider error '8007000e' Not enough storage is available to complete this operation. /dir/asp_page.asp, line xx -- Most of the time the page works fine, but sometimes I receive the error. I can´t see any particular detail in the query when the error happens (so I can´t repeat the ...

  1. #1

    Default Provider error '8007000e' - Not enough storage ...

    Hi,

    I have an asp page that uses ADO to send a query to an
    sql server and puts the result data in a recordset. Then I
    use:

    --
    variable = result_recordset.getrows(number_of_rows)
    --

    to put the result data in a variable, but in that line
    sometimes I receive the error message:

    --
    Provider error '8007000e'

    Not enough storage is available to complete this
    operation.

    /dir/asp_page.asp, line xx
    --

    Most of the time the page works fine, but sometimes I
    receive the error. I can´t see any particular detail in
    the query when the error happens (so I can´t repeat the
    error, it happens in an aleatory way). When the error
    appears, it continues appearing any time I try to run the
    page again, then I have to restart the IIS service, and
    all goes normal again. At the time the error happens, the
    server has plenty of ram and hard disk space free, the cpu
    has low load, and nothing seems strange but the behavior
    of the page. The problem started to appear early this year
    when the line above was added to the site to manage the
    recordset using a variable in ram. The amount of data
    received by the recordset could be huge, but the error not
    necesary appears with the biggest querys (last one happens
    with a 1000 rows query). The web server is a Windows 2000
    Advanced Server, the database server is a Windows 2000
    Advanced Server with SQL Server 2000.

    Anybody knows what is hapening?

    Thanks in advance,
    Claudio Ledermann

    Claudio Ledermann Guest

  2. #2

    Default Re: Provider error '8007000e' - Not enough storage ...

    "aleatory"
    Wow!

    Anyways, I've never seen this error. Let's check Aaron's site ... Ok, I
    found this page, [url]http://www.aspfaq.com/show.asp?id=2131[/url], which led to
    [url]http://support.microsoft.com/?kbid=182423[/url] ... nope - that article says this
    problem was corrected in ADO 2.0 and later (and besides, you weren't using
    appendChunk - there weren't any binary columns involved, were there?) ...
    let's see ... You're using Win2K server, so you should have ADO 2.8 ... I
    think ... You should get MDAC 2.8 and install it just in case.

    I'm striking out here. A Google search reveals one unanswered question about
    this (exactly the same issue you are having).

    Outside of the standard advice: Make sure you are properly closing and
    destroying your ADO objects; I have nothing to offer. You may need to bring
    MS PSS into the picture. If they determine that a bug was involved, you
    won't get charged for the call.

    In the meantime, I'm going to run this by the rest of the mvp's to see if
    anyone has any ideas.

    Bob Barrows


    Claudio Ledermann wrote:
    > Hi,
    >
    > I have an asp page that uses ADO to send a query to an
    > sql server and puts the result data in a recordset. Then I
    > use:
    >
    > --
    > variable = result_recordset.getrows(number_of_rows)
    > --
    >
    > to put the result data in a variable, but in that line
    > sometimes I receive the error message:
    >
    > --
    > Provider error '8007000e'
    >
    > Not enough storage is available to complete this
    > operation.
    >
    > /dir/asp_page.asp, line xx


    Bob Barrows Guest

  3. #3

    Default Re: Provider error '8007000e' - Not enough storage ...

    "Bob Barrows" <reb_01501> wrote in message
    news:uRr7O23ZDHA.2284TK2MSFTNGP12.phx.gbl...
    > "aleatory"
    > Wow!
    >
    > Anyways, I've never seen this error. Let's check Aaron's site ... Ok,
    I
    > found this page, [url]http://www.aspfaq.com/show.asp?id=2131[/url], which led to
    > [url]http://support.microsoft.com/?kbid=182423[/url] ... nope - that article says
    this
    > problem was corrected in ADO 2.0 and later (and besides, you weren't
    using
    > appendChunk - there weren't any binary columns involved, were there?)
    ....
    > let's see ... You're using Win2K server, so you should have ADO 2.8
    .... I
    > think ... You should get MDAC 2.8 and install it just in case.
    >
    > I'm striking out here. A Google search reveals one unanswered question
    about
    > this (exactly the same issue you are having).
    >
    > Outside of the standard advice: Make sure you are properly closing and
    > destroying your ADO objects; I have nothing to offer. You may need to
    bring
    > MS PSS into the picture. If they determine that a bug was involved,
    you
    > won't get charged for the call.
    >
    > In the meantime, I'm going to run this by the rest of the mvp's to see
    if
    > anyone has any ideas.
    >
    > Bob Barrows
    Bob-

    You actually replied to someone getting the exact same error message.
    The poster was pretty obnoxious though so very likely you've suppressed
    the memory of it. Here's the related post.

    [url]http://groups.google.com/groups?threadm=uLVauqnZDHA.1816%40TK2MSFTNGP09.phx .gbl[/url]

    Anyway, for the original poster (OP), here's a direct link to a possible
    cause for the error you're getting:

    See the "Can't connect to a remote machine because of RPC issue" section
    here:
    [url]http://www.gotdotnet.com/team/csharp/learn/whitepapers/howtosolvedebuggerproblems.doc[/url]

    The article is actually discusses reason why debugging in VS7(Visual
    Studio .NET) many not work. But the error message is the same so perhaps
    the resolution is as well. I expect there will be quite a few of these
    types of posts in the coming days if the cause ends up being the RPC
    patch for the Blaster virus.

    HTH
    -Chris



    Chris Hohmann Guest

  4. #4

    Default Re: Provider error '8007000e' - Not enough storage ...

    Chris Hohmann wrote:
    > "Bob Barrows" <reb_01501> wrote in message
    >
    > You actually replied to someone getting the exact same error message.
    > The poster was pretty obnoxious though so very likely you've
    > suppressed
    > the memory of it. Here's the related post.
    >
    >
    [url]http://groups.google.com/groups?threadm=uLVauqnZDHA.1816%40TK2MSFTNGP09.phx .gbl[/url]
    >
    > Anyway, for the original poster (OP), here's a direct link to a
    > possible
    > cause for the error you're getting:
    >
    > See the "Can't connect to a remote machine because of RPC issue"
    > section
    > here:
    >
    [url]http://www.gotdotnet.com/team/csharp/learn/whitepapers/howtosolvedebuggerproblems.doc[/url]
    >
    > The article is actually discusses reason why debugging in VS7(Visual
    > Studio .NET) many not work. But the error message is the same so
    > perhaps
    > the resolution is as well. I expect there will be quite a few of these
    > types of posts in the coming days if the cause ends up being the RPC
    > patch for the Blaster virus.
    >
    Ah. Good eye. I usually stop reading posts as soon as I've determined that
    they refer to dotnet. Anyways, I was searching specifically for GetRows and
    "Not Enough storage".
    ....
    OK, I've read the white paper, but I can't see how RPC is involved in this
    problem. I realize you are not saying that "this is definitely the problem".
    I'm simply offering my thoughts about it.

    Opening the recordset may involve RPC, but I'm pretty sure GetRows doesn't
    (I could be wrong here, of course - please set me straight if I am). My
    search for 8007000e turned up quite a few reasons for this error that
    involved neither RPC nor GetRows. Aaron's site has a few mentioned.

    Bob


    Bob Barrows Guest

  5. #5

    Default Re: Provider error '8007000e' - Not enough storage ...

    "Bob Barrows" <reb_01501> wrote in message
    news:eYqEEB9ZDHA.3248tk2msftngp13.phx.gbl...
    >
    > Ah. Good eye. I usually stop reading posts as soon as I've determined
    that
    > they refer to dotnet. Anyways, I was searching specifically for
    GetRows and
    > "Not Enough storage".
    > ...
    > OK, I've read the white paper, but I can't see how RPC is involved in
    this
    > problem. I realize you are not saying that "this is definitely the
    problem".
    > I'm simply offering my thoughts about it.
    >
    > Opening the recordset may involve RPC, but I'm pretty sure GetRows
    doesn't
    > (I could be wrong here, of course - please set me straight if I am).
    My
    > search for 8007000e turned up quite a few reasons for this error that
    > involved neither RPC nor GetRows. Aaron's site has a few mentioned.
    >
    > Bob
    Yeah, the RPC thing is probably a red herring. I just seems like a big
    coincidence. Blaster + RPC + Same error message. For the OP, I would
    suggest doing the following:

    1. Run the same ADO code locally on the database server. It doesn't have
    to be ASP. You can do it in VB, Access (VBA), WSH, etc... Basically, I'd
    like to determine if the problem is related to the fact that the
    database is being accessed remotely.

    2. I know this is a vague question, but can list any changes to the
    configurations of either server. Specifically, have has the
    security/permissions model changed? Are you using domains? Integrated NT
    Auth on IIS? Active Directories?

    3. Can you reproduce the error with a simplified code sample? Can you
    show us more of the code in question?

    Sorry for the poor answer/question ratio.
    -Chris


    Chris Hohmann Guest

  6. #6

    Default Re: Provider error '8007000e' - Not enough storage ...

    "Claudio Ledermann" <cledermann> wrote in message
    news:057901c36830$7f990700$a301280aphx.gbl...
    > A little more info about the scenario:
    >
    > I have a large database of products (600.000 products
    > aprox.) runing on the SQL Server 2000 - Windows 2000
    > server, and I have an asp site for add, edit, delete and
    > search products (among other things) running on the other
    > Windows 2000 server and executing sp and querys on the SQL
    > Server remotely. A big part of the site is related to
    > search tasks, and I have two search pages (named normal
    > and advanced search) that leads to the same result table
    > page. This page has a button to download the result data
    > that leads to the page with the problem. This page
    > receives the query (for historical reasons I can't use a
    > stored procedure here), send it to the database, receives
    > the result in a recordset, pass the data from the
    > recordset to a variable, destroy all database stuff, gives
    > some format to the data, writes it to a file, and show the
    > link to the user. Why passing the recordset data to a
    > variable? To free database resources in heavy downloads,
    > for example a 30.000 products download that could take up
    > to a minute to finish. Now I'm thinking in changing the
    > code and use the recordset instead of a variable to
    > generate the file
    >
    > About your questions:
    >
    > 1.- I can do that, in fact I can put the site and database
    > on the same server, a test server for example. The problem
    > is that I don't know how to generate the error. On the
    > production servers there are six people working with the
    > site plus external people doing the same (no more than ten
    > people in a day), and sometimes the problem appears twice
    > a week, and sometimes once a month.
    >
    > 2.- The changes on the servers are only the service packs
    > when they are availabe, the security/permissions model has
    > changed only giving or taken permissions to the database
    > users or creating new database users, we don't use
    > integrated NT auth on IIS, and we don't use active
    > directorys.
    >
    > 3.- I can't reproduce the error, I haven't find any
    > parameter or something to trigger it. This is a very
    > simplified pseudo code sample of the page:
    >
    > ---
    > get_parameters_and_initialize()
    > var_cmd = get_query_string()
    > var_recordset = execute_query()
    > var_recordset.filter = "some_data"
    > var_data = var_recordset.getrows(number_of_records)
    > close_and_destroy_all_data_objects()
    > open_file()
    > write_file_headers()
    > loop {
    > write_to_file( format( current_record ) )
    > next_record()
    > }
    > close_file()
    > generate_html()
    > ---
    >
    > Thanks,
    > Claudio Ledermann
    >
    Here's are some more questions:
    1. What is the value of number_of_records?
    2. Since you close_and_destroy_all_data_objects, I assume next_record
    simple refers to iterating to the next row in the resultant GetRows
    array. Correct?
    3. How are you cleaning up the temporary files you've created?
    4. Can you show the actual code and describe the Product table
    structure? It's difficult to diagnose your problem using pseudo-code

    -Chris


    Chris Hohmann Guest

  7. #7

    Default Re: Provider error '8007000e' - Not enough storage ...

    >-----Original Message-----
    >Here's are some more questions:
    >1. What is the value of number_of_records?
    >2. Since you close_and_destroy_all_data_objects, I assume
    next_record
    >simple refers to iterating to the next row in the
    resultant GetRows
    >array. Correct?
    >3. How are you cleaning up the temporary files you've
    created?
    >4. Can you show the actual code and describe the Product
    table
    >structure? It's difficult to diagnose your problem using
    pseudo-code
    >
    >-Chris
    >
    1.- The value of number_of_records is
    var_recordset.recordcount if lower than a constant (to
    limit the download size), or the value of the constant if
    higer. The constant is now set to 4000, but soon will be
    10000 or 20000.

    2.- Yes (I speak spanish, and I couldn't find a better
    word, sorry). By the way, your question leads me to
    another solution: to use a disconnected recordset. With
    this I can free the database resources and I wouldn't need
    to use a variable. It doesn't solve the strange problem,
    but the site should work fine.

    3.- They are not cleaned, they are overwritten with every
    new download. The filename is made using the username of
    each user, so they are unique, and keeping them in disk
    allow the users to download their files whenever they need
    it (knowing the url). I'm not creating any other file than
    this.

    4.- The asp file has lots of code, so I clean it up for
    better reading. I also cut the comments, because they are
    in spanish. About the table structure, it's a little bit
    more difficult to post it, because the query uses six
    basic product tables plus brand, owner, permissions and
    other business rules tables, completing a ten or more
    table query, depending on the parameters. Here is the asp
    code:

    ---
    comando_sql = session("cmd")

    set conn = Server.CreateObject ("ADODB.Connection")
    conn.ConnectionTimeout = Application
    ("Base_Ean_ConnectionTimeout")
    conn.CommandTimeout = Application
    ("Base_Ean_CommandTimeout")
    set rec_set = CreateObject("ADODB.RecordSet")
    rec_set.CursorLocation = 3 ' adUseClient
    conn.Open Application("Base_Ean_ConnectionString")
    rec_set.open comando_sql, conn

    if todas = false then
    rec_set.filter = "codemp=" & miempresa
    end if

    filas = rec_set.recordcount
    cols = rec_set.fields.count

    If filas > MAX_FILAS Then
    numfilas = MAX_FILAS
    Else
    numfilas = filas
    End If

    rec_set.movefirst
    resultado = rec_set.getrows(numfilas)

    rec_set.Close
    conn.close
    set rec_set = nothing
    set conn = nothing

    set fsys = CreateObject("Scripting.FileSystemObject")
    set salida = fsys.CreateTextFile(archivo, True)

    salida.writeline Formatear_fecha_hora (Now())
    salida.writeline( "Código" & separador & "Tipo" &
    separador & "Tipo de producto" & separador & "Código de
    empresa" & separador & "Empresa" & separador
    & "Procedencia" & separador & "Tipo de unidad" & separador
    & "Código unidad contenida" & separador & "Cantidad de
    unidades contenidas" & separador & "Código interno" &
    separador & "Descripción" & separador & "Código de
    categoría" & separador & "Categoría" & separador & "Código
    de marca" & separador & "Marca" & separador & "Modelo" &
    separador & "Envase" & separador & "Contenido" & separador
    & "Unidad de medida" & separador & "Duración" & separador
    & "Alto" & separador & "Ancho" & separador & "Fondo" &
    separador & "Peso bruto" & separador & "Peso drenado" &
    separador & "Máxima apilabilidad" & separador & "Talla" &
    separador & "Color" & separador & "Fecha de ingreso" &
    separador & "Fecha de actualización" & separador
    & "Revisado" & separador & "Fecha de revisión" & separador
    & "Cantidad de camadas" & separador & "Cantidad de
    unidades por camada" & separador & "Perfumancia" &
    separador & "Inflamabilidad" & separador & "Temperatura
    mínima de almacenamiento" & separador & "Temperatura
    máxima de almacenamiento" & separador & "Fleje
    (descripción corta para P.O.S.)" & separador
    & "Características" & separador & "Dispositivo de
    seguridad" & separador & "¿Tiene foto?" )

    formatear_resultado(resultado, numfilas)

    for y = 0 to numfilas-1

    salida.writeline( _
    resultado(0, y) & separador _
    & resultado(tipcod, y) & separador _
    & resultado(tipprod, y) & separador _
    & resultado(codemp, y) & separador _
    & resultado(emp, y) & separador _
    & resultado(proc, y) & separador _
    & resultado(tipuni, y) & separador _
    & resultado(unicont, y) & separador _
    & resultado(cantcont, y) & separador _
    & resultado(interno, y) & separador _
    & resultado(desc, y) & separador _
    & resultado(codcat, y) & separador _
    & resultado(cat, y) & separador _
    & resultado(codmarca, y) & separador _
    & resultado(marca, y) & separador _
    & resultado(modelo, y) & separador _
    & resultado(envase, y) & separador _
    & resultado(cont, y) & separador _
    & resultado(unimed, y) & separador _
    & resultado(durac, y) & separador _
    & resultado(alto, y) & separador _
    & resultado(ancho, y) & separador _
    & resultado(fondo, y) & separador _
    & resultado(peso, y) & separador _
    & resultado(pesodre, y) & separador _
    & resultado(maxapi, y) & separador _
    & resultado(talla, y) & separador _
    & resultado(color, y) & separador _
    & resultado(fecing, y) & separador _
    & resultado(fecact, y) & separador _
    & resultado(rev, y) & separador _
    & resultado(fecrev, y) & separador _
    & resultado(cam, y) & separador _
    & resultado(unicam, y) & separador _
    & resultado(perf, y) & separador _
    & resultado(infl, y) & separador _
    & resultado(tmin, y) & separador _
    & resultado(tmax, y) & separador _
    & resultado(fleje, y) & separador _
    & resultado(car, y) & separador _
    & resultado(disp, y) & separador _
    & resultado(foto, y) _
    )

    next

    salida.close
    set salida = nothing
    set fsys = nothing
    ---

    Thanks,
    Claudio Ledermann

    Claudio Ledermann Guest

  8. #8

    Default Re: Provider error '8007000e' - Not enough storage ...

    Instead of generating/overwriting temporary files, consider streaming
    the file content directly to the Response object. Here's some code:

    <%
    Response.Expires = 0
    Response.Clear
    Response.AddHeader "Content-Disposition",
    "attachment;filename=PutYourFileNameHere.txt"
    Response.ContentType="text/plain"
    Response.Write Now & vbCRLF

    Dim COL_DELIM : COL_DELIM = vbTAB
    Dim ROW_DELIM : ROW_DELIM = vbCRLF
    Const NUM_ROWS = 10000
    Dim sql,cn,rs,i,iMax,sColHdr

    sql = "PUT YOUR SQL STATEMENT HERE"
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "PUT YOUR DSN-LESS OLEDB CONNECTION STRING HERE"
    Set rs = cn.Execute(sql,,&H1)
    With rs
    If .EOF Then
    Response.Write "No Data"
    Else
    sColHdr = ""
    iMax = .Fields.Count - 1
    For i = 0 To iMax
    sColHdr = sColHdr & COL_DELIM & .Fields(i).Name
    Next
    Response.Write Mid(sColHdr,Len(COL_DELIM)+1) & ROW_DELIM
    Do While Not .EOF
    Response.Write .GetString(2,NUM_ROWS,COL_DELIM,ROW_DELIM)
    Loop
    End If
    End With
    rs.Close : Set rs = Nothing
    cn.Close : Set cn = Nothing
    Response.Flush
    %>

    Notes:
    1. Once you have the above code working, you can modify it to use the
    SQL statement you are passing via session("cmd")
    2. Instead of using a session variable, you could simple pass the SQL
    statement via a form variable.
    3. The above solution should scale up nicely. I don't imagine 600,000
    records would be a problem at all.
    4. I would definitely recommend turning your dynamically constructed SQL
    statement into a stored procedure. Also it is likely that your existing
    query could be simplified/improved in the process.

    HTH
    -Chris


    Chris Hohmann Guest

Similar Threads

  1. Server object error 'ASP 0177 : 8007000e'
    By NB in forum ASP Components
    Replies: 5
    Last Post: October 24th, 10:08 AM
  2. Provider cannot be found error
    By Peak in forum ASP Database
    Replies: 3
    Last Post: December 27th, 12:26 PM
  3. Replies: 4
    Last Post: October 22nd, 10:02 AM
  4. Provider error?
    By Ray at in forum ASP
    Replies: 10
    Last Post: August 1st, 12:49 AM
  5. Replies: 2
    Last Post: July 1st, 02:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139