Unanswered: Retrieving temp table data from MS SQL using ASP
I am trying to retrieve data from a stored procedure in MS SQL 2000 that has had to use temporary tables in coming up with the required dataset. However, when I call the procedure from a web page in which I am using VB script to call and retrieve the data, the recordset is always closed when I try to use it.
'Establish a connection with data source.
Set cnn = Server.CreateObject("ADODB.Connection")
'Instantiate a Recordset object.
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "aTest1 '" & Session("DataYear") & "'"
rs.Open strSQL, cnn
' ANY statement that trys to use the dataset rs gets the following error
' Operation is not allowed when object is closed
DO UNTIL rs.EOF <--- crash here
Response.Write rs.Fields("FundDocument") & vbCrLf
Set rs = nothing
There MUST be a way to return data from MS SQL that used temp tables to gather and format the records... but I have had no luck at all. HELP!?!?!?
like I said in the example code... it complains that the recordset is closed when I try to do anything with it after a call to any stored procedure that used temp tables. The exact error message says 'Operation is not allowed when object is closed'. I am perplexed.
You have to make sure you're doing a SELECT out of that temp table at the end of your stored procedure if you want it returned to your record set.
It should look similar to this:
CREATE PROCEDURE dbo.spMyStoredProc
--Define your inbound parameters
SET NOCOUNT ON
--Declared a bunch of variables
CREATE TABLE #tempTable (col1 numeric, col2 varchar(50))
--Do some work to find rows, and insert them into the temp table
SELECT * FROM #tempTable
DROP TABLE #tempTable
SET NOCOUNT OFF
Re: Retrieving temp table data from MS SQL using ASP
I had been experiencing the same problem myself. I used the Set NoCount On and Set No Count Off but this still did not work for me. But when using this and also putting the code into a transaction this worked. I hope it also solves your problem.
My code now looks something like the following:
CREATE PROCEDURE sp_StoredProcName
@Param1 As varchar(10),
@Param2 As int