Operation is not allowed when the object is closed.

dalezjc · July 22nd, 2010, 09:05 AM

I'm getting a " Operation is not allowed when the object is closed. " error when trying to execute my ASP code. Could it be related to the stored procedure I"m calling? Code follows:

Code:

Dim rsqdb
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
set rsqdb = server.CreateObject("ADODB.Recordset")
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myservers;Initial Catalog=nqccdb;UID=test;PWD="
set rsqdb = conn.Execute ("CREATE TABLE #temp  " _
& "     (  " _
& "       tid INT IDENTITY(1, 1),  " _
& "       mgname NVARCHAR(255),  " _
& "       ksname NVARCHAR(255),  " _
& "       tablename NVARCHAR(255)  " _
& "     )  " _
& " INSERT  #temp  " _
& "         SELECT  M.Name,  " _
& "                 P.KSGName,  " _
& "                 M.ServerTableName  " _
& "         FROM    dbo.ManagementGroup M  " _
& "                 INNER JOIN dbo.MPDefinition P ON P.ManagementGroupID = M.ManagementGroupID  " _
& "         ORDER BY M.Name,  " _
& "                 P.KSGName  " _
& " DECLARE @sql NVARCHAR(4000)  " _
& " DECLARE @ctr INT,  " _
& "     @max INT  " _
& " DECLARE @mgname NVARCHAR(255),  " _
& "     @ksname NVARCHAR(255),  " _
& "     @table NVARCHAR(255)  " _
& " CREATE TABLE #results  " _
& "     (  " _
& "       [Management_Group] NVARCHAR(255),  " _
& "       [KSG] NVARCHAR(255),  " _
& "       [Server] NVARCHAR(128)  " _
& "     )  " _
& " SELECT  @ctr = 1,  " _
& "         @max = MAX(tid)  " _
& " FROM    #temp  " _
& " WHILE @ctr <= @max  " _
& "     BEGIN  " _
& "         SELECT  @mgname = mgname,  " _
& "                 @ksname = ksname,  " _
& "                 @table = tablename  " _
& "         FROM    #temp  " _
& "         WHERE   tid = @ctr  " _
& "         SELECT  @sql = N'  " _
& "    insert #results  " _
& "     select distinct [Management_Group] = @my_mgname, [KSG] = @my_ksname, [Server] = C.Computer  " _
& "     from ' + @table + N' C  " _
& "     order by [Management_Group], [KSG], C.Computer'  " _
& "         EXEC sp_executesql @sql,  " _
& "             N'@my_mgname nvarchar(255), @my_ksname nvarchar(255)',  " _
& "             @my_mgname = @mgname, @my_ksname = @ksname  " _
& "         SELECT  @ctr = @ctr + 1  " _
& "     END  " _
& " DROP TABLE #results  " _
& " DROP TABLE #temp ")
if rsqdb.EOF then
 Response.Write "There is no data"
 Response.End
end if

Thanks!
Dale

Old Pedant · July 22nd, 2010, 03:45 PM

Pure guess: Because you forgot to put a

Code:

SET NOCOUNT ON

line into your SQL Server code, your query is actually returning *several* recordset objects, but most of them are, indeed, closed.

Try putting SET NOCOUNT ON as the first line of the SQL code.

dalezjc · July 22nd, 2010, 04:16 PM

Added the SETNOCOUNT ON, but still getting the same error. Any other suggestions?

Thanks!
Dale

Old Pedant · July 22nd, 2010, 05:07 PM

What happens if you execute this same query in a query tool? (MMS, etc.)?

I'm *guessing* that you are getting multiple recordsets, but I sure don't know that.

You could try doing this, in place of
if rsqdb.EOF then

Code:

On Error Resume Next
    Do
        Set temp = rsqdb
        Set rsqdb = rsqdb.NextRecordset
    Loop Until rsqdb IS Nothing
    Set rsqdb = temp
On Error GoTo 0
If rsqdb.EOF Then
...

Something like that?

dalezjc · July 23rd, 2010, 12:52 PM

Old Pendant,

Thanks for the tip, it worked! One more question. I'm getting the following error now:

Microsoft VBScript runtime error '800a000d'
Type mismatch /det2/show_afnet_mgs.asp, line 181

Which points to this line:

<%response.write rsqdb("Management_Group")%>

I've tried [Management_Group], and @my_mgname, both generate the same error.

Dale

Old Pedant · July 23rd, 2010, 05:21 PM

You sure it's that line??

Sometimes, ASP error line numbers are one or two off.

You could try this for debugging:

Code:

    Set fld = rsqdb("Management_Group")
    Response.Write "field type is " & fld.Type & "<br/>"
    Response.Write "typename is " & TypeName(fld.value) & "<br/>"

See what that tells you.

But show more code. Show from line 170 to 190 or so.

dalezjc · July 26th, 2010, 09:34 AM

Okay, I'm dense. I don't know where to put the Set fld = rsqdb("Management_Group") line. I've tried all over the script I keep getting a Variable is undefined: 'fld' error.

Suggestions?
Thanks!

Old Pedant · July 26th, 2010, 03:14 PM

Quote:

Originally Posted by dalezjc

Okay, I'm dense. I don't know where to put the Set fld = rsqdb("Management_Group") line. I've tried all over the script I keep getting a Variable is undefined: 'fld' error.

Suggestions?
Thanks!

My code was intended to REPLACE the line you showed:

Code:

<%response.write rsqdb("Management_Group")%>

You apparently are using OPTION EXPLICIT, so you just need to DIM the variable, thus:

Code:

<%
Dim fld
Set fld = rsqdb("Management_Group")
Response.Write "field type is " & fld.Type & "<br/>"
Response.Write "typename is " & TypeName(fld.value) & "<br/>"
%>

to replace the problem line. NOTE: THIS IS TEMPORARY, just to diagnose the problem.

dalezjc · August 9th, 2010, 11:33 AM

Got it. Thanks!

jake007 · October 4th, 2010, 04:51 AM

Old Pedant, I was breaking my head over a similar issue and your tip helped me tremendously so just wanted to say thanks.
Jake

Quote:

Originally Posted by Old Pedant

What happens if you execute this same query in a query tool? (MMS, etc.)?

I'm *guessing* that you are getting multiple recordsets, but I sure don't know that.

You could try doing this, in place of
if rsqdb.EOF then

Code:

On Error Resume Next
    Do
        Set temp = rsqdb
        Set rsqdb = rsqdb.NextRecordset
    Loop Until rsqdb IS Nothing
    Set rsqdb = temp
On Error GoTo 0
If rsqdb.EOF Then
...

Something like that?