BUG: Multiple Cursors Inside Explicit Trans. Can Cause AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

Using multiple cursors inside an explicit transaction and updating the table on which a cursor is declared causes a thread-level handled access violation (AV) during FETCH.

WORKAROUND

Avoid using multiple cursors inside an explicit transaction or use implicit transactions. This access violation happens only when the table is updated inside an explicit (user-defined) transaction.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

This problem does not occur in Microsoft SQL Server version 6.5.

MORE INFORMATION

The access violation occurs on the FETCH statement on repetitive cursor queries that open and close multiple cursors and update another table using one open cursor. A sample code fragment may look like:

begin tran declare cursor as select col1 from table1 fetch one row while @@fetch_status=0 begin

       declare another cursor on another table
       do some processing with local variables
       close and deallocate
       update table1.colx with some info, maybe from above local variables
       fetch next ---> this is where the AV occurs