I've encountered a mystifying phenomenon in executing the same ASP code (VBScript) on our two servers, test and production.

A SELECT query produces a list of medical procedures performed on patients, and associated reports (if any). The SELECT statement accumulates data from multiple tables using a LEFT OUTER JOIN clause, so that procedures which have no associated report will be listed. On the TEST server, the listed procedures include those which have no associated reports; the same code executed on PROD server does not display these procedures. I can't understand how or why two identically-configured IIS servers would produce different results using the same SELECT statement, but they are.

The code is exactly the same on each of the two boxes; the servers, while not identical hardware, are as close to identical software configuration as possible, even given that one is a test box. NT4, SP6a, IIS4...

The db is MS-Access97; I'm using a system-DSN to hit it. I've triple-checked that MDAC is the same on both boxes (v2.5SP1, as per db vendor specs). Comcheck results on TEST server showed 3 files are slightly newer than expected; PROD server show no discrepancies at all (1st time I've ever seen that happen). Regardless, I ran "comcheck /d" on PROD anyway, reconfigured / reinstalled MDAC 2.5SP1, just in case - no such luck.

The SELECT statement is listed below...

SELECT Demographics.Patient_ID, Demographics.SS_Patient_ID, " _
& "Demographics.Last_Name, Demographics.First_Name, " _
& "Event_Cath.SS_Event_Cath_ID, Event_Cath.Date_of_Cath, " _
& "Report_List.Title, Report_List.Status, Report_List.Event " _
& "FROM (Demographics INNER JOIN " _
& "(Event_Cath LEFT OUTER JOIN Report_List " _
& "ON (Event_Cath.SS_Event_Cath_ID = Report_List.SS_Event_ID " _
& "AND Report_List.Event = 'Event_Cath')) " _
& "ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) " _
& "WHERE Demographics.Patient_ID = '" & Request("Pt_ID") & "' " _
& "ORDER BY Date_of_Cath DESC

Any reasonable theories will be appreciated & graciously entertained.

you want ideas? here's one:

did you run it on exactly the same data?

try this: clear your test database, and copy some representative data down from the production database

if you still get different results, then it's a software issue, and i cannot help you any further than what you already have tried (in fact, i would not have been able to advise you on even half of the stuff you've already done)


rudy

Thank you - but the data source is common for both web servers. The data doesn't reside on either of these servers, it's on another server.

Because the web app specs are read-only, I've taken the risk of testing on "production" data. I actually do have a test installation of the db, but I use it only when doing more potentially "dangerous" development work.

okay, well, sorry, i have no further ideas

your query looks okay, by the way

hmm, same code, same data...

what does that leave?

let us know if you find the answer

still looking for an answer - I'm also out of ideas. am planning to set up a 3rd web server, just like the other 2, to confirm that something is wrong with our PROD box...

I found the answer - I had to install JET 4.0 SP 6... here's the link to download Jet4SP6:

http://www.microsoft.com/downloads/d...DisplayLang=en

PREREQUISITE: the above is an upgrade only, requires full installation of Jet 4.0 SP 3 before upgrading. Link for Jet4SP3:

http://www.microsoft.com/downloads/d...DisplayLang=en