Microsoft KB Archive/282831

= BUG: Access Violation Occurs on DECLARE CURSOR with Correlated Subquery and KEYSET or DYNAMIC Cursor Type =

Article ID: 282831

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q282831



BUG #: 55675 (SQLBUG_70)



SYMPTOMS
An Access Violation (AV) may occur on an sp_cursoropen or DECLARE CURSOR statement if the following conditions are met:
 * A SELECT statement involves a correlated subquery.


 * The correlation is through either an IN or an OR clause, with at least two different columns to compare to on the right side.


 * A non-clustered primary key is on both correlated tables.


 * The subquery table does not have any additional clustered indexes.


 * The cursor type is KEYSET or DYNAMIC. This problem does not occur with INSENSITIVE, STATIC, or FAST_FORWARD cursors.

The SELECT statement works fine outside the cursor.



WORKAROUND
To work around this behavior:
 * Replace PRIMARY KEY NONCLUSTERED with PRIMARY KEY CLUSTERED. -or-


 * Add a clustered index to the correlated subquery table. -or-


 * Change the cursor type to STATIC, FAST_FORWARD or INSENSITIVE. -or-


 * Rewrite the SELECT statement to avoid the correlated subquery.



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



MORE INFORMATION
Here is an example of a stack dump that you may find in the SQL Server error log file that corresponds to this particular bug: 2000-11-28 10:54:06.39 spid9   Error: 0, Severity: 19, State: 0 2000-11-28 10:54:06.39 spid9   SqlDumpExceptionHandler: Process 9 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. .
 * BEGIN STACK DUMP:
 * 11/28/00 10:54:06 spid 9
 * Exception Address = 006A4627 (CSubRuleCrsFtchToUnionAllStrategy::BuildSubstitutes + f6)
 * Exception Code   = c0000005 E
 * Access Violation occurred reading address 0000004E
 * Exception Address = 006A4627 (CSubRuleCrsFtchToUnionAllStrategy::BuildSubstitutes + f6)
 * Exception Code   = c0000005 E
 * Access Violation occurred reading address 0000004E

Short Stack Dump 0x006a4627 Module(sqlservr+2a4627) (CSubRuleCrsFtchToUnionAllStrategy::BuildSubstitutes+f6) 0x004f96a6 Module(sqlservr+f96a6) (COptContext::PexprTransformTopLevel+199) 0x004f93c5 Module(sqlservr+f93c5) (COptContext::PexprNormalize+22) 0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0) 0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0) 0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0) 0x004f8358 Module(sqlservr+f8358) (CMemo::NormalizeQuery+174) 0x004f7fdf Module(sqlservr+f7fdf) (COptContext::PexprSearchPlan+bb) 0x00447eef Module(sqlservr+47eef) (COptContext::PcxteOptimizeQuery+7eb) 0x0044693b Module(sqlservr+4693b) (CQuery::Optimize+21f) 0x004464b3 Module(sqlservr+464b3) (CQuery::Prepare+92) 0x0044b6bd Module(sqlservr+4b6bd) (CCvtTree::PqryFromTree+814) 0x0044b3be Module(sqlservr+4b3be) (BuildQueryFromTree+61) 0x005d863e Module(sqlservr+1d863e) (CFetchKeyset::CompileQuery+60) 0x005d8a81 Module(sqlservr+1d8a81) (CFetchKeyset::CompileSetFetchExp+56c) 0x005d8e8c Module(sqlservr+1d8e8c) (CFetchKeyset::CompileKeysetExprs+18) 0x005d8ff0 Module(sqlservr+1d8ff0) (CFetchKeyset::Compile+10f) 0x005d62ee Module(sqlservr+1d62ee) (CFetchComponent::smInitializeFetchComponent+fc) 0x005c47d2 Module(sqlservr+1c47d2) (CCursorDeclareStmt::Init+286) 0x005c6c1e Module(sqlservr+1c6c1e) (CompileCursor+1f8) 0x00530f0e Module(sqlservr+130f0e) (CCompPlan::FCompileStep+e79) 0x0043d00f Module(sqlservr+3d00f) (CProchdr::FCompile+5d9) 0x0040ec53 Module(sqlservr+ec53) (CSQLSource::FTransform+234) 0x00456463 Module(sqlservr+56463) (CSQLStrings::FTransform+159) 0x0040e7b9 Module(sqlservr+e7b9) (CSQLSource::Execute+11d) 0x00456187 Module(sqlservr+56187) (language_exec+39c) 0x41061253 Module(opends60+1253) (execute_event+659) 0x4106144e Module(opends60+144e) (process_commands+f3) 0x41092a15 Module(ums+2a15) (ProcessWorkRequests+ed) 0x410932cb Module(ums+32cb) (ThreadStartRoutine+139) 0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce) 0x77f04ede Module(KERNEL32+4ede) (lstrcmpiW+be) --- 2000-11-28 10:54:07.52 spid9   Error: 0, Severity: 19, State: 0 2000-11-28 10:54:07.52 spid9   language_exec: Process 9 generated an access violation. SQL Server is terminating this process. .

Keywords: kbbug kbpending KB282831

-

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

© Microsoft Corporation. All rights reserved.