ASP error when returning recordset from proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP error when returning recordset from proc

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2001-11-30 : 17:55:55

When using the command object to call a proc from asp that contains the following type of query:

CREATE TABLE #Temp(Temp_ID INT IDENTITY PRIMARY KEY,
Entry_ID INT)

INSERT INTO #Temp(Entry_ID)
SELECT Entry_ID
FROM SomeTable
WHERE store_date > '01/01/2001'

SELECT * FROM #Temp T
LEFT JOIN SomeTable ST ON T.Entry_ID = ST.Entry_ID

You get the following error when calling this proc:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

The reason why is because there are 2 selects in the proc. One to populate the temp table and one to join the temp table to your target table. Neither recordset is returned to ASP and thus the error is generated. I have run into this before but I just can't remember what the fix is. Does anyone know how to just return the 2nd recordset to ASP?

Thanks,
Chris

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-30 : 17:58:39

Put SET NOCOUNT ON at the top of the procedure:

CREATE PROCEDURE myProc AS
SET NOCOUNT ON
CREATE TABLE #Temp(Temp_ID INT IDENTITY PRIMARY KEY,
Entry_ID INT)

INSERT INTO #Temp(Entry_ID)
SELECT Entry_ID
FROM SomeTable
WHERE store_date > '01/01/2001'

SELECT * FROM #Temp T
LEFT JOIN SomeTable ST ON T.Entry_ID = ST.Entry_ID

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-30 : 17:59:17

add

set nocount on

at the beginning of the procedure

cbrinson
Starting Member

36 Posts

Posted - 2001-11-30 : 18:10:18

Thank you very much guys. I knew it was something easy. I have only been on this site 2 days now and I have to say that this is an excellent resource. Hopefully I will be able to help out other members at some point instead of just posting questions. Thanks again!

Chris

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-30 : 18:22:22

quote:
Hopefully I will be able to help out other members at some point instead of just posting questions.

That's how many of us got our start here on SQLTeam

. Just keep coming back and reading up on info, you'd be amazed how much you can absorb from other people's questions.

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources