Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    15

    Unanswered: inconsistent OUTER JOIN results from 2 servers

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Mar 2003
    Posts
    15
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Mar 2003
    Posts
    15
    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...

  6. #6
    Join Date
    Mar 2003
    Posts
    15

    RESOLVED: inconsistent OUTER JOIN results

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •