May 14, 2012 at 8:24 am
In my Access front end VBA code opens ADO recordset from stored procedure on remote SQL Server 2008. It worked OK for 2 years and SUDDENLY it stopped working. The code execution stops at line (see below) with popup:
Run Time errror '3704' Operation is not allowed when the object is closed
On the SQL Server side the procedure runs without problems
Call ConnectToDB <<=== connects to SQL Server database
Call SetCommand <<=== sets ADO command 'cmd'
Call SetRecordset(rsDb)
cmd.ActiveConnection = connSql
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_rCasesEnteredSummary"
cmd.Parameters.Append cmd.CreateParameter("@ReturnValue", adInteger, adParamReturnValue, 3)
cmd.Parameters.Append cmd.CreateParameter("@HospitalID", adVarWChar, adParamInput, 50, strHospital)
cmd.Parameters.Append cmd.CreateParameter("@EventSrartYear", adInteger, adParamInput, , lngEventStartYear)
cmd.Parameters.Append cmd.CreateParameter("@EventStartMonth", adInteger, adParamInput, , intEventStartMonth)
cmd.Parameters.Append cmd.CreateParameter("@lngEventEndYear", adInteger, adParamInput, , lngEventEndYear)
cmd.Parameters.Append cmd.CreateParameter("@intEventEndMonth", adInteger, adParamInput, , intEventEndMonth)
cmd.Execute
intRetVal = cmd.Parameters(0).Value
rsDb.Open cmd
If rsDb.EOF Then <<=== line the code stooped
MsgBox "There is no data for this hospital"
rsDb.Close
Exit Sub
Else
Any idea WHY the recordset does not open anymore?
Here is the proc (a little bit sloppy but simple)
ALTER Procedure [dbo].[sp_rCasesEnteredSummary]
@HospID nvarchar(50),
@sYear integer,
@sMonth integer,
@eYear integer,
@eMonth integer
As
declare
@startDate nvarchar(12),
@endDate nvarchar(12)
set @startDate=CAST(@sMonth as nvarchar )+'/1/'+CAST(@sYear as nvarchar)
set @endDate=CAST((@eMonth ) as nvarchar )+'/1/'+CAST(@eYear as nvarchar)
print @startdate
print @enddate
SELECT YEAR(EventDate) yr, MONTH(EventDate) MonthNum,
DATENAME(month,EventDate) MonthName,
DATENAME(month,EventDate) + ', '+ cast(YEAR(EventDate) as CHAR(4)) MonthYear ,
case RecommendCaseReview
When 1 then 'Recommended Case Review'
When 0 then 'Did Not Recommend Case Review'
Else 'Pt Safety Spec has not yet edited case in Clinical Review'
end AS RecCaseRev,
COUNT(*) as cnt,
h.HospitalName + ' Hospital' HospitalName
FROM tblClinicalReview t Inner Join lstHospital h
on t.HospitalID=h.HospitalID
WHERE t.EventDate Is Not Null
And t.HospitalID=@HospID
And (EventDate > CAST(@startDate as date)
AND EventDate<=CAST(@endDate as date))
Group By
YEAR(EventDate), MONTH(EventDate),DATENAME(month,EventDate),
DATENAME(month,EventDate)+ ', '+ Cast(YEAR(EventDate) as CHAR(4)),
RecommendCaseReview,h.HospitalName
Order By 1, 2, 3
May 14, 2012 at 9:06 am
Since the error is occurring in your VBA code, not your T-SQL code, you're more likely to get help from a VBA forum.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 14, 2012 at 9:12 am
Can be any sort of problem and it's unlikely to be related to SQL.
Can you execute this stored proc from SSMS?
May 14, 2012 at 9:20 am
It IS an ADO problem. The procedure runs smoothly in SSMS. The mystery is that on Friday there was no problem with ADO either - and now ...
May 14, 2012 at 9:25 am
I would think that ADO has a problem with oppening the connection to SQL server as per your "Friday's" environment.
DNS changed?
May 14, 2012 at 9:47 am
Nothing was changed (I like your motto re microsoft - this is just another example ...)
May 14, 2012 at 10:06 am
If nothing has changed, it would continue to work.
So, something has changed! (It doesn't work now - it's a change!)
Is this only bit doesn't work? Any other parts of code which connect to db and calls procedures still working?
Any change in security? What authentication are using?
May 14, 2012 at 10:39 am
OK. I'll try to run other ADO / command / recordset codes to see if there is a system problem.
Thanks
May 14, 2012 at 11:29 am
I do have some familiarity with VBA and ADO (though it has been awhile).
What I cannot help noticing is that the Parameter names used in your VBA code do not match those listed for your stored procedure? I cannot remember for sure if they must match because I have always made them match, so that's something to check out.
That aside, the logical inference from the information that you have given us is that the "rsDB.Open cmd" statement right before your EOF check must have failed to return an open rowset, BUT somehow did not throw an exception, thus causing the next statement (the EOF check) to throw an exception instead.
The only thing that I know that can cause a recordset.Open to return a closed recordset without throwing an exception, is if the stored procedure itself does not return a rowset. And that takes me back to the matter of the parameter names, or anything else that might have caused to sProc to fail.
What I'd recommend, is to use the SQL Profiler to trace your app's call to the stored procedure, so that you can see exactly what the call is trying to do (or if it is even getting that far).
(edit: typos corrected)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2012 at 7:11 am
Thanks to all. The intriguing fact is that the app that worked smoothly for 2 years SUDDENLY stopped working. It RESUMED working 6 hours after I posted this post. We opened the app on another machine, it worked ok, after that it resumed working on mine Well ...
=================================
I do not have a choice for the front end other than Access. And my experience (in addition to this one) is that Access front end brings up surprises. I will list some:
1) Recordset does not open from ADO command (this one)
2) (N)varchar (max) does not go thru the ADO recordset unless not casted as text (bad choice keeping in mind that text would be obsolete)
3) If some mysterious characters precede (n)varchar entry it does not show up in the Access controls (mysterious because users enter them, but in the intellisense you see them as small squares. In the SQL table you see them as spaces that cannot be ltrimmed. )
=================================
I would appreciate if you share your experiences with this front/back end configuration
Val
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply