Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   MySQL (https://www.askmehelpdesk.com/forumdisplay.php?f=442)
-   -   Sql 2000 sp3 issue (https://www.askmehelpdesk.com/showthread.php?t=1634)

  • Nov 26, 2003, 07:45 AM
    tadds
    SQL 2000 SP3 Issue
    REWARD: $40.00 to anyone who can solve this. Microsoft is having difficulty with the resolution!!!

    The Problem... :-/
    The problem experienced when we upgraded to SQL Server 2000 and is isolated to a very obscure set of circumstances.  When a nested recordset is created using the same table in both the parent and child recordsets AND that table has a primary key on an identity column which is not covered by the table's non-unique clustered index AND the connection variable is re-used when opening each recordset THEN SQL Server 2000 (but not 7.0) implicitly converts the child recordset to a server-side cursor and somehow looses track of, or just ignores, the key column HENCE producing a WHERE clause for the child recordset's update that does NOT contain the primary key column THUS generates a "-2147467259 Key column information is insufficient or incorrect. Too many rows were affected by update."

    The Workaround...
    There are at least three ways to fix this specific problem (not including the obvious re-design options).  When the connection is reset for each recordset, or when the "Update Criteria" property of the child recorset is set to "adCriteriaAllCols", or when the primary key column is covered by the clustered index (unique or not, doesn't matter), the problem goes away!

    The Challenge...
    Although we have gotten our process to run, we still don't know what the full impact on the rest of the app is.  So, we need to know exactly WHY this behavior changed when we upgrade to SQL Server 2000.  Personally, I'm leaning toward this being an undocumented bug in SQL Server 2000 SP3.  But, I haven't been able to find anything definitive on it yet.  So, I'm forwarding the attached files (below) to Microsoft to get them working on  it.  

    Hint...
    This issue does NOT appear to be ADO-version-related.  I've tested it every way possible using ADO 2.5, ADO 2.6 and ADO 2.7 - all gave same results.

  • All times are GMT -7. The time now is 04:17 PM.