Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Extra nextrecordset for sp_help in ASP

    I'm running "sp_help mytable1" from ASP against SQL Server 2000 over ADODB. Other T-SQL commands work fine, including commands giving more than one result set. But, for this very command, the 6th and 7th record sets give:

    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.

    when I try to fetch the data.

    I have a loop that, for any returned record sets, displays all data.
    If I, instead of looping over all record sets, code one data-displaying loop per record set (i.e. a "manual" variant), the sp_help call works provided that I, before displaying the 6th and 7th record set, code an extra "set rs = rs.nextrecordset". I.e., for those record sets giving the above error, I have to do an extra nextrecordset.
    So far, it's only with sp_help tablename this has occurred.

    The general loop works fine in VB6 including the two problem recordsets. It's only in ASP that record set no. 6 and 7 are inaccessible without extra Next's.

    Any hints???

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post your asp code ?

  3. #3
    Join Date
    Jul 2002
    Posts
    229
    ASP code (the general one) follows below.
    Enter sp_help mytable1 in a HTML form calling it.

    <html>



    <%

    REM *************************
    REM Subrutiner
    REM *************************

    REM Print a recordset's field details
    sub printfields

    FOR EACH FIELD IN rs.FIELDS
    response.write("<p>Field name: " & FIELD.NAME)
    response.write("<br>Type: " & LTRIM(cSTR(FIELD.TYPE)))
    response.write("<br>Defined size: " & LTRIM(cSTR(FIELD.DEFINEDSIZE)))
    response.write("<br>Actual size: " & LTRIM(cSTR(FIELD.ACTUALSIZE)))
    NEXT

    end sub

    REM *************************
    REM Huvudrutinen
    REM *************************

    REM *** Create connection ***

    dim conn

    set conn = createobject("ADODB.Connection.2.6")
    conn.provider = "SQLOLEDB"
    conn.connectionstring = "data source=myserver1;
    initial catalog=mydatabase1;Integrated Security=SSPI;Trusted_Connection=Yes;"

    REM *** Open and run ***

    conn.open
    set rs = conn.execute( request.form("sqlcmd"))

    REM *** Output to web client ***

    response.write("<u>" & request.form("sqlcmd") & "</u><br>")
    REM printfields

    do until rs is Nothing

    do until rs.eof
    for each field in rs.fields
    response.write("<br>" & field.name & " = " & rs(field.name))
    next
    response.write("<br>--------------------------------")
    rs.movenext
    loop

    set rs = rs.nextrecordset

    loop



    REM *** Close connection ***

    conn.close
    set conn = nothing

    %>

    </html>

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    I *think* I've solved it by checking if the next recordset is closed - if so, do an extra next recordset. Code included below. Still don't understand why the recordset get a State value of "closed".

    (How do I write an "if object is NOT nothing then..." ?)

    <html>
    <%

    REM *** Create connection ***

    dim conn

    set conn = createobject("ADODB.Connection.2.6")
    conn.provider = "SQLOLEDB"
    conn.connectionstring = "data source=myserver1;
    initial catalog=mydatabase1;
    Integrated Security=SSPI;Trusted_Connection=Yes;"

    REM *** Open and run ***

    conn.open
    set rs = conn.execute( request.form("sqlcmd"))

    REM *** Output to web client ***

    response.write("<u>" & request.form("sqlcmd") & "</u><br>")

    REM do until rs.state = adStateClosed
    do until rs is Nothing

    do until rs.eof
    for each field in rs.fields
    response.write("<br>" & field.name & " = " & rs(field.name))
    next
    response.write("<br>--------------------------------")
    rs.movenext
    loop

    REM next recordset

    set rs = rs.nextrecordset

    REM if there is a next recordset, check if it's closed and if so, go to next after this
    REM Don't know why it's needed.

    if rs is nothing then
    response.write("nop")
    else

    if rs.state = adStateClosed then

    set rs = rs.nextrecordset

    end if

    end if

    loop

    REM *** Close connection ***

    conn.close
    set conn = nothing

    %>

    </html>

Posting Permissions

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