Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Washington, USA
    Posts
    7

    Angry Unanswered: Simple UPDATE to static value doesn't work. Why?!?

    Greetings,

    I have a situation where I'm trying to UPDATE the value of one column in all rows of an MSDE 2000-hosted table. I'm currently using Access 2K2 with a .ADP as a front-end. Once the kinks are ironed out, it'll be migrated to an ASP/ADO setup (i.e., keep the MSDE, lose the Access 2K2).

    What I've come to after a fair amount of experimentation is that MSDE is broken (OK, maybe I'm doing something wrong). Currently the table is ~45,000 rows long (though only ~10K rows have non-zero data in the field I'm trying to set to zero).

    I've found that the following does not work (this is pasted from Access 2K2's 'SQL View' of my Stored Proc):
    Code:
    ALTER PROCEDURE "Zero_Totals"
    AS
    UPDATE D
    SET D.Total = 0
    Unfortunately, this seems to execute lightning-fast, but turns out to be no update at all. This same SQL worked just dandy before I upsized from Jet to MSDE (as an 'Update Query' rather than a stored proc, under Jet), but now it says, "The stored procedure executed successfully but did not return records." That sounds good, but the fact is that it did nothing. I know it didn't work because when I consult the table, it shows that there are still non-zero values in the Total column.

    But wait! It gets weirder:
    I copied ~5K rows with non-zero values to a test table and ran my SP against that; all rows were updated to zero. Then I did the same with ~15K rows, ~25K rows, and ~35K rows. None of them worked except for the ~5K row test. However, the query is affecting some rows, for example, on my last run (against the ~25K row table) the total non-zero rows before updating was 3513, and afterwards it was 1839. AAAHHHH!!!!

    Also, if I append a WHERE clause to the above UPDATE statement and specify a row to update (by it's key), the update works fine (though it only updates one record). However, if I use
    'WHERE Key IN (SELECT D.Key FROM D)'
    as a WHERE clause, it doesn't work any better than with no WHERE clause at all. So, it seems to work in some situations, but not all, and I don't feel like I can rule out any possibility.

    The irregularity and inconsistency of this are driving me insanse, I'm totally stumped. My co-worker suspects that MSDE is broken or limited in some outrageous way, and I don't want to believe him, but I'm at a complete loss to explain this.

    Does anyone know of any circumstances that could account for what I've described? Are there any circumstances under which SQL Server/MSDE would asynchronously execute an SP (accept the command and return control before it was done, but not complete it until later)?

    Then again, if it were just asynchronous execution, what is MSDE doing returning a success message? And why doesn't the table update after fact? No, asynchronous execution seems a highly unlikely explanation.

    This looks like a deal-breaker to me - if I can't resolve it, I'm going to have to abandon MSDE and go with Jet. I'm frustrated because this is obviously such a simple operation that I'm sure I'm doing something stupid wrong, but I just can't see it.

    Suggestions? Comments?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I don't use MSDE but I did catch one thing that was interesting. You said that everything worked when you had about 5K records but bombed with more records. This tells me that your code is correct and maybe you have a limitation in MSDE. I know MSDE was designed for development and NOT for production or other heavy use so you may have a process that would work great on Std Edition or higher.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    Washington, USA
    Posts
    7

    Angry Something stupid discovered

    Well, I've discovered what was going wrong. I'm shocked that no one else has had the same problem - I can't find reference to it anywhere.

    It turns out that, if you are using an Access XP SP1 .ADP with MSDE (and presumably SQL Server) you must be aware of the 'Default max records' value in the 'Tools -> Options -> Advanced' dialog.

    I don't understand why this option would affect the scope of an UPDATE stored procedure, but it does.

    Just wanted to follow up with the hope that maybe this will help someone else avoid the pain and annoyance of going through this.

Posting Permissions

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