November 23, 2005 at 12:04 am
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.
November 23, 2005 at 2:34 pm
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?
November 23, 2005 at 10:23 pm
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.
November 24, 2005 at 1:47 am
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
November 24, 2005 at 4:40 am
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
November 24, 2005 at 5:28 am
November 24, 2005 at 7:36 am
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
November 24, 2005 at 7:46 am
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......
November 24, 2005 at 8:25 am
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
November 24, 2005 at 8:28 am
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?
November 24, 2005 at 8:32 am
HI,
Sorry to bother you....
But it does not take any parameter. I just call the SP and it gives me the result for 12 months. The same SP when I run through Query Analyser on Client server through "EXEC SPname" it works fine.
Same data Same Calling statement just the medium of calling is different.
Again, when I call it thorough my VB code the SP run till 4 months and at 5months it stops. If at 5months it is hanging then it should happen same when I run through Query Analyser at Client Server.
Please Help
November 24, 2005 at 8:37 am
Is it timing out, either on SQL Server or on the ADO connection object (they are separate settings)?
November 24, 2005 at 8:54 am
Hi,
I have set this at Infinite ( connection timeout = 0)
And that too at both end. At Sql Server also and in ADO connection also. BUT 🙁
Please don't be harsh.... but this a problem that I am facing. Also my senior programmer had hand on it but it VAIN.
Please help dear....
November 24, 2005 at 9:58 am
You say it stops or hangs. Does SQL batch actually finish and return partial results or is it in a wait state or locking problem? Although the message that you quoted was ADO, perhaps there is something in the SP. Would you post the SP, please.
December 14, 2005 at 7:22 am
use Set nocount on
before Insert statement
and set nocount off
after Insert Statement
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply