Stored procedure problem in Classic ASP

I am trying to execute the code below. When I execute the exact same code in the Query Analyzer, I get 6 records so there are records to be found. But when I try to do it through ASP, I get Error Number 3704:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

I used the same connection string to execute other stored procedures with this user id without problem in this database. For some reason it is dying here.

The user id has execute rights on this procedure.

I’ve already spent half a day trying to get this snippet to work right. If I had any hair left, I would be pulling it out.

Any help would be appreciated.

Ramon

Here’s the code:

re: Stored procedure problem in Classic ASP

OK.

First rename your connection.

"Connection" is probably not a good name because I think it is a reserved word for vbscript.

Rename it to MyConn or something.....

Try that.

If that doesnt work let me know, and also please tell me which line this error is occurring on.

re: Stored procedure problem in Classic ASP

I changed the code to MyConnection but I get the same result.

The error happens between
response.write "1 b_Error=" & b_Error & "<br>" & Err.number & "<br>"
where Err.number =0

response.write "2 b_Error=" & b_Error & "<br>" & Err.number & "<br>"
where Err.number = 3704

I put the If rs.EOF = True Then code to precipitate an error if there is an empty recordset. I tried rs.BOF and still get the same results.

The point is that the stored procedure should be generating results (6 records), but I am getting this empty recordset error instead.

re: Stored procedure problem in Classic ASP

This is not the error of an empty recordset

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed

If that is the error you are getting something else is wrong here.

This code should prevent the application from crashing which you have in there.
So if you are getting "Operation is not allowed when the object is closed" what line is that happening on?

If I am misunderstanding and the only problem is that you have an empty recordset, then I can only say there is something wrong with the stored procedure, or something different about executing the procedure outside the application.

I can't tell you with what you have supplied why your stored procedure is returning 0 records. Especially since I can't see the SP , nor the tables effected.

re: Stored procedure problem in Classic ASP

The error is happening on the If rs.Eof = True statement.

It does the execute sql fine without generating an error but when I try to do anything with the results it generates the error that acts as if the recordset was not created in the first place.

So while you are correct that the error does not relate to an empty recordset, the effect works out to be the same.

I can post the stored procedure if you like, but it is going to be a lot of code.

re: Stored procedure problem in Classic ASP

Well before you do that lets try to isolate the problem

We can do this by replacing your stored procedure (and nothing else) with an SQL string.

Basically just pass a simple SQL string instead of the SP. See if there are any problems.

If you get records back, then the problem is NOT in your code but is either a environment problem (can't run SP for some reason from app) or the way the SP is called , etc.

It sounds obvious but this is an easy way to point directly to the SP or the application environment as the problem area rather then the code.

re: Stored procedure problem in Classic ASP

Looking at the stored procedure I see that it has a temporary table.

A. Can I make a single SQL stament that has a temporary table? Never tried it before.

B. I have had problems before with this id and stored procedures that had temporary tables. So this could be the crux of the problem.

As I understand it, temporary table are created in the master database. Does the id need some special permissions there to be able to create temporary tables?

re: Stored procedure problem in Classic ASP

you can't do exactly the same thing, but you can approximate it. give more details of how your temp table is set up and I will try to give pointers.quite possibly you need read write and create permissions.

Jared