Stored procedure problem in Classic ASP

[Krandor]

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:

Dim Connection
Dim ConnString
Dim rs
Dim SQL
Dim b_Error
 
ConnString = "Provider=sqloledb;Data Source=<demo>;" & _
"Network Library=DBMSSOCN;Initial Catalog=<demo>;" & _
"User ID=<demo>;Password=<demo>;"
 
Set Connection = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
 
Connection.Open ConnString
 
b_Error = False
On Error Resume Next
rs.Open "exec <demo>.dbo.txp_report_contract_set 2672,'N00104-02-G-0600-5036'",Connection
response.write "1 b_Error=" & b_Error & "<br>" & Err.number & "<br>"
 
If rs.EOF = True Then 
'Just tests to see if rs.EOF = True causes an error which is captured below.
End If
 
If Err.number <> 0 then
b_Error = True
End If
response.write "2 b_Error=" & b_Error & "<br>" & Err.number & "<br>"
response.end

[jeffstl]

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.

[Krandor]

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.

[jeffstl]

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.

if RS.EOF = True then
     'captured
     response.write "No Records"
     response.end
end if
 

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.

[Krandor]

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.

[jeffstl]

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.

'Instead of stored procedure do this
dim TestSQL
TestSQL = "Select * from AnyTable"
 
RS.Open TestSQL,MyConnection
 
if RS.EOF then
     'same problem still exists
else
     'there MUST be some kind of problem with running the SP from the app
end if
 

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.

[Krandor]

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?

[jhardman]

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

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.

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?

quite possibly you need read write and create permissions.

Jared