BUG: Attentions When Using Text Based Insensitive/Keyset Cursors can Result in Closetable Errors (238336)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q238336
BUG #: 18814 (SQLBUG_65)

SYMPTOMS

When using insensitive/keyset cursors on text columns, closetable errors may occur if the cursors receive attentions.

For the closetable errors to occur, the following sequence of events need to occur:
  • A server process id (spid) performs a sp_cursoropen and other sp_cursor related operations on a SELECT of a text column based on a join.
  • This spid gets an attention in the midst of performing these operations.
  • The spid then processes sp_cursorclose on the previously mentioned cursor.
  • Next, the spid opens a cursor on a SELECT that does not have to include any text/image columns but does require the creation of a worktable.
  • Closes the cursor.
  • The spid opens another cursor on a SELECT using a text column.
At this point the closetable errors are written into the errorlog: 
1999/07/20 15:48:34.43 spid1    Launched startup procedure 'sp_sqlregister'
1999/07/20 15:49:03.31 spid32   closetable: table already closed for sdes 35faee50
1999/07/20 15:49:03.42 spid32   closetable: table already closed for sdes 35faee50

WORKAROUND

To work around the behavior, use these steps:
  1. Change the cursor type to dynamic or forward-only.

    -or-
  2. Run SQL Server with -T247.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

MORE INFORMATION

Here is an excerpt from a SQL Trace output that shows this pattern (the trace corresponds to the preceding excerpt from the errorlog):

EventSPIDStartTimeSQL Stmt.
PreSQL3219990720 15:48:59:513set quoted_identifier on use "reprodb2"
PreSQL3219990720 15:48:59:530exec sp_server_info 500 select 501,NULL,1 where 'a'='A' select 504,c.name,c.description,c.definition from master.dbo.syscharsets c,master.dbo.syscharsets c1,master.dbo.sysconfigures f where f.config=1123 and f.value=c1.id and c1.csid=c.id set textsize 2147
PreSQL3219990720 15:48:59:543SELECT @@SPID
PreRPC3219990720 15:48:59:560sp_cursoropen NULL, "SELECT EXCLUDE_CARFAX FROM BRANDING", 8, 1, NULL
PreRPC3219990720 15:48:59:920sp_cursorfetch 271190832, 256, 256, 256
PreRPC3219990720 15:48:59:936sp_cursorclose 271190832
PreRPC3219990720 15:48:59:950sp_cursoropen NULL, "SELECT PAGE_CONTENT FROM PAGES, CATEGORIES WHERE PAGE_ID = 108", 8, 1, NULL
PreRPC3219990720 15:48:59:966sp_cursoroption 271242032, 1, 1
PreRPC3219990720 15:48:59:966sp_cursoroption 271242032, 1, 1
PreRPC3219990720 15:49:00:250sp_cursorfetch 271242032, 16, 1, 1
PreRPC3219990720 15:49:00:263sp_cursorfetch 271242032, 256, 256, 256
PreRPC3219990720 15:49:00:700sp_cursoroption 271242032, 3, 1
PreRPC3219990720 15:49:01:390sp_cursor 271242032, 40, 1
PreRPC3219990720 15:49:01:543sp_cursor 271242032, 40, 1
Attention3219990720 15:49:01:686NULL
PreRPC3219990720 15:49:01:873sp_cursorclose 271242032
PreRPC3219990720 15:49:02:826sp_cursoropen NULL, "SELECT RESULT_PAGE_ID FROM SUB_CATEGORIES ORDER BY SUB_CATEGORY_DESCRIP", 8, 1, NULL
PreRPC3219990720 15:49:03:186sp_cursorclose 271850288
PreRPC3219990720 15:49:03:263sp_cursoropen NULL, "SELECT EXCLUDE_CARFAX FROM BRANDING", 8, 1, NULL


SPID 32 gets the first attention at 15:49:01:686. At that point the spid was doing some cursor operations on a select based on a text column and a join: 
SELECT PAGE_CONTENT FROM PAGES, CATEGORIES WHERE PAGE_ID = 108
The same spid closes out the preceding cursor, and opens another one on a non-text column that requires a worktable: 
SELECT RESULT_PAGE_ID FROM SUB_CATEGORIES ORDER BY SUB_CATEGORY_DESCRIP
SPID 32 closes out the preceding cursor, and opens another text based cursor: 
SELECT EXCLUDE_CARFAX FROM BRANDING
This is where the closetable errors start showing up in the errorlog.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB238336
©2003 Microsoft Corporation. All rights reserved.