Page only working sporadically

Apologies first for not pasting code for this - I normally do my postings on the HTML/CSS forum, so I'm new to this bit of CodingForums.

I think it's a problem with a shared server one of my sites is hosted on, and I'm trying to gauge whether anyone else has had a similar problem as it's driving me wild!

I've got a classic ASP page which is pulling recordsets out of an Access 2003 database file - there's no clever .NET trickery as I'm a bit old fashioned that way. The site is written using Dreamweaver CS3.

On my client's hosting server (http://www.healthcarealliances.co.uk/Public/newsite/default.asp), sometimes it works, sometimes it doesn't. When it doesn't work, the line that's reported with an error changes each time I hit the refresh button in my browser, and eventually the page will load again. The line numbers correspond to the last line of
Set [recordsetName]_cmd = Server.CreateObject ("ADODB.Command")
[recordsetName]_cmd.ActiveConnection = [reference to connection string]
[recordsetName]_cmd.CommandText = "[SQL statement]"
[recordsetName]_cmd.Prepared = true
Set [recordsetName] = [recordsetName]_cmd.Execute so it's failing at the various points at which it's pulling the data into recordsets (the "Set [recordsetName] = [recordsetName]_cmd.Execute") - I'm pulling about about half a dozen recordsets out.

The page works fine on my development server, and on another shared server with a different hosting company (http://www.designstage.co.uk/healthcarealliances/default.asp), it also works.

As far as I can tell, all three servers are running Windows 2003 Server R2/ IIS6/ .NET1.1 so I'm scratching my head. I'm not really up on server setups, so I was wondering if it was possible that the client's host server might be really busy, and the errors because the page was timing out, but then being parsed correctly when the server was quiet again.

I can feel that I'm clutching at straws - diagnosing my specific problem from what I've said is probably not possible. I guess I'm just after hints or suggestions that I could follow up with my client's hosting company.

Ok yeah the error I'm getting is:

Microsoft JET Database Engine error '80004005'

Unspecified error

/Public/newsite/default.asp, line 262

... or on line 289.

I suspect that there are two contributing factors here.

One is that you're using Access, which isn't a proper database server. Access doesn't cope too well when you try to do lots of stuff all at once to it. Especially if there are lots of other people using the site, who are also trying to do lots of stuff to it at the same time.

The other is that you're possibly not talking to Access in the most efficient manner. If you're opening and closing a connection each time you get a recordset out, for example, your Access database is going to get very confused very quickly.

So... if you could post some more of your code, we might be able to suggest ways you could make it more efficient and friendlier to your database. But I'd also recommend that you switch to a proper database server (like MySQL for instance) if at all possible.

HTH

I had a horrible feeling someone would want to look at the code - I'm very reliant on Dreamweaver for dealing with database connections (they've always worked so it's not something I've delved into too deeply), and I know how I tell people on the CSS forum that there's nothing like hand-coding their CSS, so I'm going to be in for a bit of a public mauling, I'm sure.

And I know I ought to step up out of the sandpit and use MS SQL like the big boys do. It's just that I've grown up with Access and it's comfortable. But I guess I'm hitting its limits of the concurrent requests - it's just strange that it works on two servers fine, but only sporadically on the client's host.

I've uploaded a ZIP file which contains the ASP, and the file that contains the connection string. I'll wait until you've finished laughing. ;) Be gentle...

ok... looking at it, I'd say one major and one not so major thing. It's not that bad code though, don't worry :)

Major thing: you're creating a lot of objects. A LOT. And you never destroy them. This is gonna be hard on your server memory for one, but I suspect that all those commands and recordsets are chewing up database connectivity.

Once you've executed your command into a recordset, you should use getRows() (http://www.devguru.com/technologies/ado/8678.asp) to turn your recordset into an array. This uses less memory for the server, and lets you kill a recordset that might otherwise still be keeping open a channel of communication to the database. I'm not 100% on that last bit, but definitely kill your objects as soon as possible. Once you've got the data into an array, you can kill your recordset, then you can kill the associated command.

So an amended version of your first query might now look like:
<%
Dim rsAdvancedProgramme
Dim rsAdvancedProgramme_cmd
Dim rsAdvancedProgramme_numRows
dim aryAdvancedProgramme

Set rsAdvancedProgramme_cmd = Server.CreateObject ("ADODB.Command")
rsAdvancedProgramme_cmd.ActiveConnection = MM_conHCA_STRING
rsAdvancedProgramme_cmd.CommandText = "SELECT CourseID, DateStart, IsInUse, Module_Name, ProgrammeTypeID, District, Organisation, VenueName FROM qryCourseShortened WHERE ProgrammeTypeID = ? AND DateStart >= Date() AND IsInUse =-1 ORDER BY DateStart ASC"
rsAdvancedProgramme_cmd.Prepared = true
rsAdvancedProgramme_cmd.Parameters.Append rsAdvancedProgramme_cmd.CreateParameter("param1", 5, 1, -1, rsAdvancedProgramme__MMColParam) ' adDouble

Set rsAdvancedProgramme = rsAdvancedProgramme_cmd.Execute

aryAdvancedProgramme = rsAdvancedProgramme.getRows()

rsAdvancedProgramme.close
set rsAdvancedProgramme = nothing
set rsAdvancedProgramme_cmd= nothing

rsAdvancedProgramme_numRows = 0
%>

Make sense?

One other thing: do a search/replace on your code for this:
%>
<%
and replace it with nothing. You're jumping in and out of ASP for no reason, and every time you do it your server takes a little hit.

All that said, I still reckon you're going to find issues running that many queries on one page against an Access database. It just wasn't designed for this sort of job.

I knew I was going to have to blow the dust off my ASP books one day... Thanks very much.

I've been in touch with their host, and they're willing to set up a MS SQL database, so I'll probably set that up too.

John

Just one more thing...

I'd thought that another approach that I was going to try was to create a single recordset of all my course data and then filter that each time I wanted the records for a particular type of course. Would this be more efficient? I'm thinking there's fewer recordsets created, but not sure whether this would be offset by the extra work filtering out the relevant records each time.

It would almost certainly be more efficient, although your SQL statement would have to be quite carefully constructed in order to both pull out the data in a useable manner AND not grind the database to a halt. As a pretty reliable rule of thumb, though; databases are much better at crunching data than any server-side scripting language. Except if it's Access, which sucks :)

I think I'm getting the message to say farewell to my beloved Access (actually only beloved until Access 2003 - Access 2007 is like kickboxing in a straightjacket).

It feels like throwing away a pair of really comfy slippers, but I think it's time for me to get over it and make the move.

Thanks for your advice Spudhead.