PROBLEM with calling Procs from ASP codes:
Any simple stored procs, i.e. without use of temporary tables (#CurTable), do work !
But those with temporary table (e.g. #CurTable) returns closed or bad recordset object
- thus is not working !!
Content of one of these SQLServer procs are as follows:

    PROCEDURE dbo.proc-name
      input parameters
    AS
      declare local varaibles
      initialize local variables
    DECLARE TableCursor CURSOR LOCAL FORWARD_ONLY STATIC FOR
      original SQL-statement
    CREATE TABLE #CurTable(own parameters)
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO field-parameters
    WHILE @@FETCH_STATUS = 0
        BEGIN
        compute value of fields for record in '#CurTable'
        INSERT INTO #CurTable(fields-list) VALUES(values-of-fields list)
        reset field values for next '#curTable' record
        FETCH NEXT FROM TableCursor INTO field-parameters
        END
    CLOSE TableCursor
    DEALLOCATE TableCursor
      update last record of '#CurTable' if necessary
    SELECT fields FROM #CurTable WHERE conds ORDER BY sort-fields
    DROP TABLE #CurTable

However, while it works calling from SQLServer Query-Analyser utility Tools,
and from Crystal Reports, can retrieve record set from ASP!
This is the ASP routine:

    Dim rs

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = adUseServer
    rs.open sql,connstr,adOpenForwardOnly,adLockReadOnly

    Do while not rs.EOF '' This gives error stating this object, rs, is not opened !
    'process a record'
    rs.MoveNext
    Loop
    rs.Close
    Set rs = nothing

THX all!!!

Jorge

__________________
Jorge

Hey,

You need to use a command object to execute a stored procedure. You can pass the results to a recordset. I don't think you can execute it directly from a recordset.

Brian

Hi Brian!
Actually, I did try what you suggest (but had not yet updated the Post)....
Alternatively, using Command object is tried (as several suggestions from forum web sites):

    Dim cmd, cn, sql, rs

    sql = "<proc-name>"

    Set cn = server.CreateObject("ADODB.Connection")
    cn.Open Application("NSIA1_ConnectionString")
    Set cmd = Server.CreateObject("ADODB.Command")
    with cmd
        .ActiveConnection = cn
        .CommandText = sql
        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("@ReportGroupId",adInteger,adPara mInput,,<1st parameter value>)
        .Parameters.Append .CreateParameter("@CurrentSeason",adInteger,adPara mInput,,<2nd parameter value>)

        .Parameters.Append .CreateParameter("@UserId",adInteger,adParamInput, ,<last parameter value>)
        Set rs = .Execute
    end with
    Set cmd = nothing

    Do while not rs.EOF '' This still gives an error stating this object, rs, is not opened !
      '' proecess a record
      rs.MoveNext
    Loop
    rs.Close()
    Set rs = nothing
    cn.Close()

THX again!

Jorge

Hey,

Try setting your cmd to nothing after you are done with the recordset. I wonder if that's how the rs gets the connection, and because you set it to nothing, that may cause a problem???

Also, double-check your query. ADO may have a problem with it, even if it works in SQL Server.

Thanks,

Brian

Your stored procedure is probably returning other stuff before the recordset.
Firstly try adding SET NOCOUNT ON after the AS statement at the start of the procedure. If that doesn't fix it try this before you use the recordset: Now start using the rcordset.

--

Joe

SOLUTION:
Here's our solution (tested successfully):
(ie Solution to calling Proc with temporary stable from ASP page)
    Dim sql, rs

    sql = "proc-name its-parameter"

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = Application("NSIA1_ConnectionString")
    rs.LockType = adLockReadOnly
    rs.CursorType = adOpenForwardOnly
    rs.CursorLocation = adUseServer
    rs.Open "SET NOCOUNT ON" '' This statement does the trick !
    rs.open sql

    Do while not rs.EOF
      ''''process current record 'rs'
      rs.movenext
    Loop
    rs.Close()
    Set rs = noting

Also see (MS knowledge Base Article 235340):
http://support.microsoft.com/default...b;en-us;235340

THX to all :) !

Jorge