Microsoft KB Archive/238336

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

Article ID: 238336

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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-
 * 1) 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):

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.

Additional query words: Cursor, sp_cursor, attention, text

Keywords: kbbug kbpending KB238336

-

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

© Microsoft Corporation. All rights reserved.