Re: Returning results from a temporary table using ADO

From: Chris (c.coverley_at_ntlworld.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 23:09:09 GMT

Hi,

    Yes it is possible.

    We're doing pretty much the same as your example except we've using a
client-side cursor. ADO throws errors about invalid object names when the
command is executed but the recordset gets populated OK so just a case of
discarding the spurious error.

    We've assumed the error is because ADO is trying to be clever and
interrogate the schema of the temporary table, which has been dropped as it
has gone out of scope.

Chris Coverley
MCSD

"Peter Hiross" <PAHiross@currentInc.com> wrote in message
news:%23PU$XSbdEHA.3732@TK2MSFTNGP11.phx.gbl...
> Hello,
>
>
>
> I 'm trying to invoke a stored procedure using ADO from a web page. For
> brevity, I have a stored procedure that creates a temporary table, does
some
> computations and then writes the result to the temporary table. Upon
> completion of the stored procedure the contents of the temporary table is
> returned.
>
> I've managed to reproduce the problem in a simplified form (Code
enclosed).
> The error manifest itself as an empty recordset or a closed recordset
(you
> will receive an error "Operation is not allowed when the object is closed"
> if you attempt to query properties from the recordset object).
>
>
>
> The test code uses basic ADO objects and builds a simple dropdown list.
Not
> rock-science. I also coded the same functionality in .NET and didn't have
> any problems.
>
>
>
> In playing around with the stored procedure I found out:
>
>
>
> - Did away with temporary table and no results changed.
>
> - When I simply the stored procedure to simply perform a query on
a
> result table (TestTmpTbl) and did not do any inserts or deletes to this
> table a valid result set was returned. If I attempted to do an insert to
the
> table and then query the table, the RS object doesn't appear to be
created.
>
> - I later broke up usp_TestTempTbl into three procedures:
>
> o Usp_PurgeTempTbl
>
> o Usp_BuildTempTbl
>
> o Usp_FetchTempTbl
>
>
>
> Within Usp_FetchTempTbl store procedure I exec the other two SP, the
results
> were the same (My thinking was to see if separate Batches could help). If
> However, I invoked each stored procedure from the ADO code it worked.
>
>
>
> So, the question is it not possible to invoke a stored procedure that
> creates and populates a temporary table and to have the results of that
> table returned?
>
>
>
> Code Enclosed below. Reproduced on SQL 2000 and Windows Server 2003.
>
>
>
> Thanks for your help in advance.
> Regards,
>
> Peter
>
>
>
> SET NOCOUNT ON
>
> GO
>
> DROP PROCEDURE usp_TestTempTbl
>
> GO
>
> CREATE PROCEDURE usp_TestTempTbl
>
> @ID INT = NULL
>
> AS
>
> CREATE TABLE #TempTbl ( GroupUID INT, SmallDesc VARCHAR(256), StrLineage
> VARCHAR(80) )
>
>
>
> INSERT INTO #TempTbl VALUES (1,'Test1','test1--test' )
>
> INSERT INTO #TempTbl VALUES (2,'Test2','test2--test' )
>
> INSERT INTO #TempTbl VALUES (3,'Test3','test3--test' )
>
>
>
> SELECT * FROM #TempTbl
>
> RETURN
>
> GO
>
>
>
>
>
> Set oConn = Server.CreateObject("ADODB.Connection")
>
> oConn.ConnectionTimeout = 3000
>
> DSN = "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User
> ID=yyy;Initial Catalog=TestDB;Data Source=Test Server;"
>
>
>
> oConn.open DSN
>
> Response.Write "Connection State: " & oConn.State
>
> set oCmd = Server.CreateObject("ADODB.Command")
>
> oCmd.ActiveConnection = oConn
>
> oCmd.CommandText = "usp_TestTempTbl"
>
> oCmd.CommandType = adCmdStoredProc
>
> ID = Request.QueryString("ID")
>
> if( ID ) then
>
> Response.Write "ID: " & ID
>
> oCmd.Parameters.Append oCmd.CreateParameter("@ID", adInteger,
> adParamInput, 4)
>
> oCmd.Parameters("@ID") = ID
>
> end if
>
>
>
> set oRS = oCmd.Execute
>
> if( oConn.Errors.Count > 0 ) then
>
> for each oErr in oConn.Errors
>
> response.Write "ERR: " & oErr.Description
>
> next
>
> end if
>
>
>
> 'if you un-comment will get error on this line. Error deals with operation
> on closed object (oRS)
>
> response.write "Record Set State: " & oRS.State & " Record Cnt: " &
> oRS.RecordCount
>
>
>
> while not oRS.EOF
>
> response.write oRS.fields("GroupUID") & ": " & oRS.fields("SmallDesc")
&
> " (" + oRS.fields("StrLineage") & ") "
>
> oRS.MoveNext
>
> wend
>
>

Relevant Pages

  • Re: How does ADO 2 update a SQL stored procedure recordset?
    ... > Generally when I want to use ADO 2 to update a recordset based on a MSSQL ... > call a second stored procedure to perform the update. ... Well if you have SELECTed the columns in the recordset that form the Primary ... You are running SQL Server. ...
    (microsoft.public.data.ado)
  • Re: Make a disconnected recordset editable
    ... The recordset was NOT updatable when I first fetched it. ... Use ADO command to run a stored procedure and get a read-only ... Convert Recordset to an XML document ... create a *new* ADO Recordset... ...
    (microsoft.public.data.ado)
  • Re: SQL Server with ADO Recordset - How to return the recordset from Stored Procedures, if it has mu
    ... Make sure you add SET NOCOUNT ON to the beginning of the stored procedure. ... ADO as an empty, closed recordset. ... > If I comment the insert and update statements, I am able to receive the ...
    (microsoft.public.sqlserver.programming)
  • Re: Make a disconnected recordset editable
    ... Use ADO command to run a stored procedure and get a read-only Recordset. ... Convert Recordset to an XML document ...
    (microsoft.public.data.ado)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)