Please help,



1.



I'm trying to use a SQL2000 Stored Proc with ASP. The proc creates a temp

table with some data /business logic and returns a recordset with a SELECT

statement with data from the temp table. It works fine in the SQL Query

Analyzer. It works fine in ASP as long as I don't try to use the resulting

recrodset in the ASP. If I try to use the recordset in ASP I always get the

message "'Operation is not allowed when the object is closed." I've used

every type of ASP means I've come across to populate the ASP recordset.

Same result every time. They all work as long as I don't try to access the

recordset. If in the proc I create a global temp table (##) instead of a

local temp table (#) and then use a second proc for the SELECT statement I

can work the recordset in ASP as long as I use a second ASP command to

access the recordset. If I try to use an EXEC proc2 in the first proc, and

only one ASP command to get the recordset in ASP it doesn't work. Is this

clear ? What am I doing wrong ? 



2.



Also when I do get it working using two procs and two ASP commands, I can't

use rs.MoveLast. I get the message "'Object doesn't support this property

or method: 'EOF'". Even when I use adOpenDynamic and adLockOptimistic. How

do I get around this ?



3.



Also, I've read somewhere that using Table variable or something is better

than using temp tables in SQL procs. But the books online doesn't explain

it well to me, no examples. And I can't find any info about this in the

dozen books I have. Can any one help me with this ? Explain how it works

and an example or two how to use it to populate an ASP recordset ?



I have a dozen books on ASP and SQL and none of them seem to explain the

above problems. Maybe I've missed the explanations ?



Thanking you in advance for your kind help.

Peter

Try using in the stored proc you have created after the AS keyword



SET nocount on

i.e 

CREATE Procedure <my_procedure>

AS

SET nocount on



That should return only the last recordset in the stored proc



> Please help,

> 

> 1.

> 

> I'm trying to use a SQL2000 Stored Proc with ASP. The proc creates a temp

> table with some data /business logic and returns a recordset with a 

SELECT

> statement with data from the temp table. It works fine in the SQL Query

> Analyzer. It works fine in ASP as long as I don't try to use the 

resulting

> recrodset in the ASP. If I try to use the recordset in ASP I always get 

the

> message "'Operation is not allowed when the object is closed." I've used

> every type of ASP means I've come across to populate the ASP recordset.

> Same result every time. They all work as long as I don't try to access 

the

> recordset. If in the proc I create a global temp table (##) instead of a

> local temp table (#) and then use a second proc for the SELECT statement 

I

> can work the recordset in ASP as long as I use a second ASP command to

> access the recordset. If I try to use an EXEC proc2 in the first proc, 

and

> only one ASP command to get the recordset in ASP it doesn't work. Is this

> clear ? What am I doing wrong ? 

> 

> 2.

> 

> Also when I do get it working using two procs and two ASP commands, I 

can't

> use rs.MoveLast. I get the message "'Object doesn't support this property

> or method: 'EOF'". Even when I use adOpenDynamic and adLockOptimistic. 

How

> do I get around this ?

> 

> 3.

> 

> Also, I've read somewhere that using Table variable or something is 

better

> than using temp tables in SQL procs. But the books online doesn't explain

> it well to me, no examples. And I can't find any info about this in the

> dozen books I have. Can any one help me with this ? Explain how it works

> and an example or two how to use it to populate an ASP recordset ?

> 

> I have a dozen books on ASP and SQL and none of them seem to explain the

> above problems. Maybe I've missed the explanations ?

> 

> Thanking you in advance for your kind help.

> Peter

>