Microsoft KB Archive/220169

= PRB: Keyset Cursor Based Off a Join Not Showing New Records =

Article ID: 220169

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q220169



SYMPTOMS
When you use keyset cursors with SQL Server and the cursor is based off a join of two or more tables and you insert new records through the cursor, the newly added records do not appear in the cursor's membership. If you close the cursor and reopen it, the newly added records are then present in the cursor's membership.



CAUSE
When SQL Server creates a keyset cursor the server creates a "keyset" table in tempdb that contains the key value of the qualifying records. Sometimes another column (timestamp value) is needed to uniquely identify a record. This "keyset" table is then used as a lookup table to retrieve records from the base table as directed by fetch operations. In cases where the keyset cursor is based off one table, newly added records are added to the membership of the cursor, or in other words, newly added records are also added to the "keyset" table used for future fetches. In cases where the cursor is based off a join of two or more tables, either through a view or through the cursor select statement, newly added records are added to the base table but the record is not added to the "keyset" table. Thus the record does not show up in future fetches from this cursor. This behavior only occurs with server-side cursors where there are two or more tables involved. Since you are using a keyset cursor you will never see rows inserted by other connections in your keyset cursor unless you close and then reopen it.



WORKAROUND
Here are two workarounds:
 * Use a dynamic cursor in place of a keyset cursor.

-or-
 * To see newly added records, close the keyset and reopen it after inserting new records.



MORE INFORMATION
Additional query words: dynaset snapshot

Keywords: kbprb KB220169

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.