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

• Microsoft SQL Server version 6.5

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.

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 version 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):

Event	SPID	StartTime	SQL Stmt.
PreSQL	32	19990720 15:48:59:513	set quoted_identifier on use "reprodb2"
PreSQL	32	19990720 15:48:59:530	exec 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
PreSQL	32	19990720 15:48:59:543	SELECT @@SPID
PreRPC	32	19990720 15:48:59:560	sp_cursoropen NULL, "SELECT EXCLUDE_CARFAX FROM BRANDING", 8, 1, NULL
PreRPC	32	19990720 15:48:59:920	sp_cursorfetch 271190832, 256, 256, 256
PreRPC	32	19990720 15:48:59:936	sp_cursorclose 271190832
PreRPC	32	19990720 15:48:59:950	sp_cursoropen NULL, "SELECT PAGE_CONTENT FROM PAGES, CATEGORIES WHERE PAGE_ID = 108", 8, 1, NULL
PreRPC	32	19990720 15:48:59:966	sp_cursoroption 271242032, 1, 1
PreRPC	32	19990720 15:48:59:966	sp_cursoroption 271242032, 1, 1
PreRPC	32	19990720 15:49:00:250	sp_cursorfetch 271242032, 16, 1, 1
PreRPC	32	19990720 15:49:00:263	sp_cursorfetch 271242032, 256, 256, 256
PreRPC	32	19990720 15:49:00:700	sp_cursoroption 271242032, 3, 1
PreRPC	32	19990720 15:49:01:390	sp_cursor 271242032, 40, 1
PreRPC	32	19990720 15:49:01:543	sp_cursor 271242032, 40, 1
Attention	32	19990720 15:49:01:686	NULL
PreRPC	32	19990720 15:49:01:873	sp_cursorclose 271242032
PreRPC	32	19990720 15:49:02:826	sp_cursoropen NULL, "SELECT RESULT_PAGE_ID FROM SUB_CATEGORIES ORDER BY SUB_CATEGORY_DESCRIP", 8, 1, NULL
PreRPC	32	19990720 15:49:03:186	sp_cursorclose 271850288
PreRPC	32	19990720 15:49:03:263	sp_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 

SELECT RESULT_PAGE_ID FROM SUB_CATEGORIES ORDER BY SUB_CATEGORY_DESCRIP 

SELECT EXCLUDE_CARFAX FROM BRANDING 

Additional query words: Cursor, sp_cursor, attention, text

Keywords : kbSQLServ650bug kbbug6.50.sp5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug