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

• Microsoft SQL Server version 7.0

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.

Additional query words: dynaset snapshot

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbprb