Cannot return results of temporary table

Hi,
I've written a stored procedure which loops over several tables, processes
the data and stores the processed results in a temporary table.
At the end of the procedure, I'd like to return a 'select *' from the
temporary table to an ASP page, from where I call the stored procedure.
The procedure works, I can execute it and view the results in my Query
Analyzer, but I get an error when I call the procedure from my ASP page.
The error I receive is 'Operation is not allowed when the object is
closed'. I've read on the net that a temporary table is closed and removed
as soon as the stored procedure ends - could this be the problem?
The line on which the error occurs is:
if not (rec.eof) and not (rec.bof) then
(the place where i first use the recordset given to me by the stored
procedure.
Anyway, I'm at a loss here as to how I can solve this problem. Any help is
greatly appreciated.
Thanks,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricksJust add "set nocount on" as the very first line after "as" in your sproc.
-oj
"jcsnippets.atspace.com" <admin@.jcsnippets.atspace.com> wrote in message
news:Xns97805DB2D943Cadminjcsnippetsatsp
a@.195.238.0.34...
> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks|||hi,
Could you please so kind to show us that code snippet?
--
current location: alicante (es)
"jcsnippets.atspace.com" wrote:

> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks
>|||"oj" <nospam_ojngo@.home.com> wrote in
news:OfUOBnoQGHA.1160@.TK2MSFTNGP09.phx.gbl:

> Just add "set nocount on" as the very first line after "as" in your
> sproc.
>
Alas, that did not solve my problem...
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks|||examnotes <Enric@.discussions.microsoft.com> wrote in
news:2E51C9AF-3A4A-4277-926E-578691A83FCF@.microsoft.com:

> hi,
> Could you please so kind to show us that code snippet?
Of course - I should have posted this right away. Here is the code - if I
leave out the code marked '-- problem --', and only do the test insert,
the code works fine.
-- Code
alter procedure log_getProductHits
(
@.custid int,
@.category int
)
as
set nocount on
begin
declare @.seq1 int
declare @.seq2 int
declare @.name1 varchar(100)
declare @.name2 varchar(100)
declare @.count1 int
declare @.count2 int
create table #temphits
(
product varchar(100),
hits int
)
insert into #temphits values ('product', 15)
-- PROBLEM --
-- Get category level 1
if (@.category is null)
begin
declare cat1 cursor for
select seq
from cat_pd_cat
where custid = @.custid
and parent_seq is null
end
else
begin
declare cat1 cursor for
select seq
from cat_pd_cat
where custid = @.custid
and parent_seq = @.category
end
open cat1
fetch cat1 into @.seq1
while (@.@.FETCH_STATUS = 0)
begin
print 'Seq1: ' + cast(@.seq1 as varchar)
-- Insert valid products
declare prod1 cursor for
select cat_pd_product.name, count(*)
from cat_dom_productlog,
cat_pd_product
where cat_dom_productlog.custid = @.custid
and cat_dom_productlog.cat_pd_cat = @.seq1
and cat_pd_product.seq =
cat_dom_productlog.cat_pd_product
group by cat_pd_product.name
order by cat_pd_product.name
open prod1
fetch prod1 into @.name1, @.count1
while (@.@.FETCH_STATUS = 0)
begin
insert into #temphits values (@.name1, @.count1)
fetch prod1 into @.name1, @.count1
end
close prod1
deallocate prod1
fetch cat1 into @.seq1
end
close cat1
deallocate cat1
-- PROBLEM --
select * from #temphits order by product
end
-- /Code
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks|||That error means that the recordset within ASP is closed. If your procedure
works in query analyzer but gives this error in ASP then the error is in
your ASP code. Verify the SQL command and all the parameters immediately
before calling your stored procedure. Output them all to the page then
execute the stored procedure in query analyzer with the displayed
parameters.
Use cut and paste to do this. The problem could be as simple as a typo, or
a parameter could be blank, etc. Your connection string could also be
invalid.
Posting your ASP code might provide some insight as well.
"jcsnippets.atspace.com" <admin@.jcsnippets.atspace.com> wrote in message
news:Xns97805DB2D943Cadminjcsnippetsatsp
a@.195.238.0.34...
> Hi,
> I've written a stored procedure which loops over several tables, processes
> the data and stores the processed results in a temporary table.
> At the end of the procedure, I'd like to return a 'select *' from the
> temporary table to an ASP page, from where I call the stored procedure.
> The procedure works, I can execute it and view the results in my Query
> Analyzer, but I get an error when I call the procedure from my ASP page.
> The error I receive is 'Operation is not allowed when the object is
> closed'. I've read on the net that a temporary table is closed and removed
> as soon as the stored procedure ends - could this be the problem?
> The line on which the error occurs is:
> if not (rec.eof) and not (rec.bof) then
> (the place where i first use the recordset given to me by the stored
> procedure.
> Anyway, I'm at a loss here as to how I can solve this problem. Any help is
> greatly appreciated.
> Thanks,
> JayCee
> --
> http://jcsnippets.atspace.com
> a collection of source code, tips and tricks|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in
news:OXvPtyrQGHA.5808@.TK2MSFTNGP12.phx.gbl:

> That error means that the recordset within ASP is closed. If your
> procedure works in query analyzer but gives this error in ASP then the
> error is in your ASP code. Verify the SQL command and all the
> parameters immediately before calling your stored procedure. Output
> them all to the page then execute the stored procedure in query
> analyzer with the displayed parameters.
> Use cut and paste to do this. The problem could be as simple as a
> typo, or a parameter could be blank, etc. Your connection string
> could also be invalid.
> Posting your ASP code might provide some insight as well.
It appears that the error was not in the ASP page, it was in the stored
procedure.
After tampering with the procedure, I found out that I should have written
select * from #temphits order by product
instead of
return select * from #temphits order by product
at the end of the stored procedure.
Thanks to everyone who took the trouble to reply!
Best regards,
JayCee
--
http://jcsnippets.atspace.com
a collection of source code, tips and tricks