SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Operation is not allowed when Object is Closed


Operation is not allowed when Object is Closed

Author
Message
Vishal Prajapati-253528
Vishal Prajapati-253528
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1

Hi,

I have a SP which gives the me the report of 12 months. I have a query in WHILE LOOP which execute 12 times my taking month number from 1 to 12 and accordingly gives the result as per each month. The result of gets store into HASH table for all 12 months.

I run the SP through Query Analyser locally and it takes around 30 - 40 sec. When I run the same SP on Client Server Query Analyser it takes around 1 min and 30 sec approx.

But same SP when I call thru VB code which has ODBC connection to get the result in the front-end it gives me following error " ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. "

When I run the Profiler I found that Query batch at 4th month have StartBatch but it does not have CompleteBatch and after which the Profiler returns nothing.

Also query time out is set to 0. And connection time of in VB code is also set to 0.

Please help.




Kindest Regards,

Vishal Prajapati

DBA at Extentia Infromation Technology

Jo Pattyn
Jo Pattyn
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27293 Visits: 10758

Can we see the code of the stored procedure.

Is the hash table a temporary table (last for one connection) or a more permanent one?


Vishal Prajapati-253528
Vishal Prajapati-253528
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1

Hi,

Here is the SP ........

ALTER PROC getaverage

AS

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

CREATE TABLE #tblAverage (
[ID] int,
[Month] varchar(100),
[Value] varchar(20))


DECLARE @NoofMonth int
SET @NoofMonth = 0


WHILE @NoofMonth < 12
BEGIN

INSERT INTO #tblAverage SELECT DISTINCT TABLE1.ID, 'Month ' + cast(@NoofMonth + 1 as varchar), count(TABLE2.User) FROM TABLE1, TABLE2

WHERE TABLE1.ID = TABLE2.ID AND DATEPART(mm,TABLE1.DT) = @NoofMonth

AND DATEPART(yy,TABLE1.DT) = '2004'

GROUP BY TABLE1.ID



SET @NoofMonth = @NoofMonth + 1
END


SELECT * FROM #tblAverage

DROP TABLE #tblAverage


SET NOCOUNT OFF
SET ANSI_WARNINGS ON

GO

------------------------------------------------

AGAIN SP WORKS FINE Locally in Query Analyser and also AT Client's Server Query Analyser.





Kindest Regards,

Vishal Prajapati

DBA at Extentia Infromation Technology

Alex Otten
Alex Otten
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 3

How did you create your query in VB?

If you are using the Recordset.Open to execute a SELECT statement you will have to close the recordset yourself, but with the INTO statement the Recordset is closed after is has been executed.

Hope this helps,

Alex Otten


Stewart Joslyn
Stewart Joslyn
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4937 Visits: 188
This looks like a problem with your VB code. Are you using a server-side cursor? If so, try making it client side. An example (omitting any parameters for the sp) :

Set l_cmdCommand = New ADODB.Command
With l_cmdCommand

.ActiveConnection = objDatabase.Connection
.CommandType = adCmdStoredProc
.CommandText = ProcedureName
.ActiveConnection.CursorLocation = adUseClient
Set l_rsRecordset = .Execute

End With



Vishal Prajapati-253528
Vishal Prajapati-253528
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1

Hi,

Thanks for you reply.....

But this does not help out as we are making use of client side cursor only......

Please help




Kindest Regards,

Vishal Prajapati

DBA at Extentia Infromation Technology

Lowell
Lowell
SSC Guru
SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)

Group: General Forum Members
Points: 286530 Visits: 41896

your error is on the vb handling side....

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

this error occurs if you refer to the recordset in vb code , but the recordset did not find any records, ie:

SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

Set rs= Conn.Execute(SQL)

sometextbox.text=rs!SomeField

The above statement will raise the error above if the rs.EOF was true:

it should be: SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

Set rs= Conn.Execute(SQL)

if not rs.eof then

sometextbox.text=rs!SomeField ' rs("SomeField").Value is the eqivilient syntax

end if

other similar error s will happen if the rs!SomeField was null, and you try to stick it directly into a variable or object property. (Invalid use of null for example);

you could also get this same error if you are using the following code:

SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

Set rs.open SQL,Conn

if you did not first do a Set rs=New ADODB.Recordset prior ti the rs.Open command; using the Conn.Execute makes that step unnecessary.



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Vishal Prajapati-253528
Vishal Prajapati-253528
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1

Hi,

Thanks for reply.....

I told in my original post that it works locally very fine. Also when the SP is run through the Query Analyser of client server, there also it run and gives the prefect result without any NULL values.

Also when I tried to figured out problem from Profiler I found that in loop after the Month 4 NO statement is executed. (Remember again SP runs fine in Query Analyser, of all 12 months it returns the result)

Please help......




Kindest Regards,

Vishal Prajapati

DBA at Extentia Infromation Technology

Lowell
Lowell
SSC Guru
SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)SSC Guru (286K reputation)

Group: General Forum Members
Points: 286530 Visits: 41896

with sample data, in QA, it works fine....i agree. it has nothing to do with the quality of your SQL/procs.

but in the real world application, your data is returning no records in certain conditions.(after month4?)

when no data is returned, your VB application are not handling the lack of data correctly.



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Stewart Joslyn
Stewart Joslyn
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4937 Visits: 188
Are you passing parameters into your stroed procedure? If so, are you sure that they are being passed correctly? Possibilities could be variable type mismatches, padding or ascii vs unicode.

Have you checked the ADO error collection?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search