BUG: Fetch From a Dynamic Cursor Causes an Access Violation (281346)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q281346
BUG #: 100921 (SQLBUG_70), 351123 (SHILOH)

SYMPTOMS

When rows are fetched from a dynamic cursor, for example: 
declare  rt_upl_all_csr cursor for
     Select rcd_id, trf_dt, srv_id, mstr_rt_id, srv_rt_id,
            rfl_fg,spcl_rat_fg,loc_id_orn,loc_id_dst,rt_dst_srv_id
        From ivs_inb_trf_w iitw
       where exists (select rcd_id from ivs_inb_trf_hop_w iithw
                      where iithw.rcd_id = iitw.rcd_id)
         and (mstr_rt_id = 0 or srv_rt_id = 0 
                             or srv_rt_id not in ( select isnull(irmd.srv_rt_id,0)   
                                                 from ivs_rt_mstr_dt irmd
                                                where irmd.mstr_rt_id = iitw.mstr_rt_id
                                                  and irmd.srv_id     = iitw.srv_id   
                                                  and irmd.loc_id_orn = iitw.loc_id_orn
                                                  and irmd.loc_id_dst = iitw.loc_id_dst
                                                  and irmd.rfl_fg     = iitw.rfl_fg
                                                  and irmd.start_dt  <= iitw.trf_dt
                                                  and irmd.end_dt    >= iitw.trf_dt 
                                                  and irmd.spcl_rat_fg = iitw.spcl_rat_fg ))
        order by rcd_id
The following access violation may occur: 
0x00402676 Module(sqlservr+2676) (RecBase::Resize+2)
0x004041fd Module(sqlservr+41fd) (RecBase::LocateColumn+11)
0x004073fe Module(sqlservr+73fe) (setkeys+64)
0x0059a8d0 Module(sqlservr+19a8d0) (MatchUserSargsToIndex+b9)
0x00405de4 Module(sqlservr+5de4) (startscan+276)
0x006c3c45 Module(sqlservr+2c3c45) (RowsetSS::GotoMarker+a8)
0x006c427d Module(sqlservr+2c427d) (RowsetMarker::GotoMarker+11)
0x006618e1 Module(sqlservr+2618e1) (CQScanRange::GotoMarker+b4)
0x0065a95c Module(sqlservr+25a95c) (CQScanFilter::GotoMarker+19)
0x0066024f Module(sqlservr+26024f) (CQScanNLJoin::GotoMarker+2b9)
0x0065a95c Module(sqlservr+25a95c) (CQScanFilter::GotoMarker+19)
0x0065641c Module(sqlservr+25641c) (CQueryScan::GotoMarker+23)
0x005dc7b0 Module(sqlservr+1dc7b0) (CCursorDynaQueryScan::GotoMarker+67)
0x005db793 Module(sqlservr+1db793) (CFetchDynamic::Fetch+2e8)
0x005c94da Module(sqlservr+1c94da) (CCursor::Fetch+23d)
0x005c73d4 Module(sqlservr+1c73d4) (CCursorFetchStmt::XretExecute+10b)
0x0040f314 Module(sqlservr+f314) (CMsqlExecContext::ExecuteStmts+11a)
0x0040ed54 Module(sqlservr+ed54) (CMsqlExecContext::Execute+16a)

WORKAROUND

Declare the cursor as either STATIC, KEYSET or INSENSITIVE as in these reworked examples:

For STATIC
==========
  declare  rt_upl_all_csr cursor STATIC for
        Select rcd_id, trf_dt, srv_id, mstr_rt_id, srv_rt_id,
               rfl_fg,spcl_rat_fg,loc_id_orn,loc_id_dst,rt_dst_srv_id
           From ivs_inb_trf_w iitw
          where exists (select rcd_id from ivs_inb_trf_hop_w iithw
                         where iithw.rcd_id = iitw.rcd_id)
            and (mstr_rt_id = 0 or srv_rt_id = 0 
                                or srv_rt_id not in ( select isnull(irmd.srv_rt_id,0)   
                                                    from ivs_rt_mstr_dt irmd
                                                   where irmd.mstr_rt_id = iitw.mstr_rt_id
                                                     and irmd.srv_id     = iitw.srv_id   
                                                     and irmd.loc_id_orn = iitw.loc_id_orn
                                                     and irmd.loc_id_dst = iitw.loc_id_dst
                                                     and irmd.rfl_fg     = iitw.rfl_fg
                                                     and irmd.start_dt  <= iitw.trf_dt
                                                     and irmd.end_dt    >= iitw.trf_dt 
                                                     and irmd.spcl_rat_fg = iitw.spcl_rat_fg ))
           order by rcd_id


For KEYSET
===========
  declare  rt_upl_all_csr cursor KEYSET for
        Select rcd_id, trf_dt, srv_id, mstr_rt_id, srv_rt_id,
               rfl_fg,spcl_rat_fg,loc_id_orn,loc_id_dst,rt_dst_srv_id
           From ivs_inb_trf_w iitw
          where exists (select rcd_id from ivs_inb_trf_hop_w iithw
                         where iithw.rcd_id = iitw.rcd_id)
            and (mstr_rt_id = 0 or srv_rt_id = 0 
                                or srv_rt_id not in ( select isnull(irmd.srv_rt_id,0)   
                                                    from ivs_rt_mstr_dt irmd
                                                   where irmd.mstr_rt_id = iitw.mstr_rt_id
                                                     and irmd.srv_id     = iitw.srv_id   
                                                     and irmd.loc_id_orn = iitw.loc_id_orn
                                                     and irmd.loc_id_dst = iitw.loc_id_dst
                                                     and irmd.rfl_fg     = iitw.rfl_fg
                                                     and irmd.start_dt  <= iitw.trf_dt
                                                     and irmd.end_dt    >= iitw.trf_dt 
                                                     and irmd.spcl_rat_fg = iitw.spcl_rat_fg ))
           order by rcd_id


For INSENSITIVE
===============
  declare  rt_upl_all_csr INSENSITIVE cursor for
        Select rcd_id, trf_dt, srv_id, mstr_rt_id, srv_rt_id,
               rfl_fg,spcl_rat_fg,loc_id_orn,loc_id_dst,rt_dst_srv_id
           From ivs_inb_trf_w iitw
          where exists (select rcd_id from ivs_inb_trf_hop_w iithw
                         where iithw.rcd_id = iitw.rcd_id)
            and (mstr_rt_id = 0 or srv_rt_id = 0 
                                or srv_rt_id not in ( select isnull(irmd.srv_rt_id,0)   
                                                    from ivs_rt_mstr_dt irmd
                                                   where irmd.mstr_rt_id = iitw.mstr_rt_id
                                                     and irmd.srv_id     = iitw.srv_id   
                                                     and irmd.loc_id_orn = iitw.loc_id_orn
                                                     and irmd.loc_id_dst = iitw.loc_id_dst
                                                     and irmd.rfl_fg     = iitw.rfl_fg
                                                     and irmd.start_dt  <= iitw.trf_dt
                                                     and irmd.end_dt    >= iitw.trf_dt 
                                                     and irmd.spcl_rat_fg = iitw.spcl_rat_fg ))
           order by rcd_id

STATUS

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

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

MORE INFORMATION

When a dynamic cursor fetch is run under a profiler this problem usually yields the following assertion: 
0x77f97ac6 Module(ntdll+17ac6) (NtGetContextThread+b)
0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
0x0059792a Module(sqlservr+19792a) (Scan_rid::GetLogicalRid+22)
0x00597bf2 Module(sqlservr+197bf2) (MatchUserSargsToIndex+85)
0x00405cc8 Module(sqlservr+5cc8) (startscan+276)
0x006c0456 Module(sqlservr+2c0456) (RowsetSS::GotoMarker+a8)
0x006c0a79 Module(sqlservr+2c0a79) (RowsetMarker::GotoMarker+11)
0x0065e24f Module(sqlservr+25e24f) (CQScanRange::GotoMarker+b4)
0x006572e0 Module(sqlservr+2572e0) (CQScanFilter::GotoMarker+19)
0x0065cbbd Module(sqlservr+25cbbd) (CQScanNLJoin::GotoMarker+2b9)
0x006572e0 Module(sqlservr+2572e0) (CQScanFilter::GotoMarker+19)
0x00652da0 Module(sqlservr+252da0) (CQueryScan::GotoMarker+23)
0x005d8c41 Module(sqlservr+1d8c41) (CCursorDynaQueryScan::GotoMarker+67)
0x005d7c0f Module(sqlservr+1d7c0f) (CFetchDynamic::Fetch+2e8)
0x005c5ac0 Module(sqlservr+1c5ac0) (CCursor::Fetch+23d)
0x005c39ba Module(sqlservr+1c39ba) (CCursorFetchStmt::XretExecute+10b)
0x004145f1 Module(sqlservr+145f1) (CMsqlExecContext::ExecuteStmts+11a)
0x0041409f Module(sqlservr+1409f) (CMsqlExecContext::Execute+16a)
2000-11-13 17:51:16.48 kernel   SQL Server Assertion: File: (scanrid.cpp), line=315 
Failed Assertion = 'm_len != 0'.
2000-11-13 17:51:16.54 spid19   Error: 3624, Severity: 20, State: 1.


Sometimes, it also produces the following Access Violation: 

Short Stack Dump
0x00404da8 Module(sqlservr+4da8) (srchpage+277)
0x004048e6 Module(sqlservr+48e6) (srchindex+1f1)
0x00406c67 Module(sqlservr+6c67) (prepscan+cd)
0x00406afc Module(sqlservr+6afc) (LinkedPageSupplier::FirstPage+1c)
0x004068c3 Module(sqlservr+68c3) (LinkedPageSupplier::GetNextPage+42a)
0x00406664 Module(sqlservr+6664) (SDES::GetBiDi+1c1)
0x006c3c5e Module(sqlservr+2c3c5e) (RowsetSS::GotoMarker+c1)
0x006c427d Module(sqlservr+2c427d) (RowsetMarker::GotoMarker+11)
0x006618e1 Module(sqlservr+2618e1) (CQScanRange::GotoMarker+b4)
0x0065a95c Module(sqlservr+25a95c) (CQScanFilter::GotoMarker+19)
0x0066024f Module(sqlservr+26024f) (CQScanNLJoin::GotoMarker+2b9)
0x0065a95c Module(sqlservr+25a95c) (CQScanFilter::GotoMarker+19)
0x0065641c Module(sqlservr+25641c) (CQueryScan::GotoMarker+23)
0x005dc7b0 Module(sqlservr+1dc7b0) (CCursorDynaQueryScan::GotoMarker+67)
0x005db793 Module(sqlservr+1db793) (CFetchDynamic::Fetch+2e8)
0x005c94da Module(sqlservr+1c94da) (CCursor::Fetch+23d)
0x005c73d4 Module(sqlservr+1c73d4) (CCursorFetchStmt::XretExecute+10b)
0x0040f314 Module(sqlservr+f314) (CMsqlExecContext::ExecuteStmts+11a)
0x0040ed54 Module(sqlservr+ed54) (CMsqlExecContext::Execute+16a)
-------------------------------------------------------------------------------
2000-11-07 10:11:50.57 spid9    Error: 0, Severity: 19, State: 0
2000-11-07 10:11:50.57 spid9    language_exec: Process 9 generated an access violation. SQL Server is terminating this process.


In SQL Server 2000 the following access violations (AVs) are produced: 
Short Stack Dump
00402453 Module(sqlservr+00002453) (RecBase::Resize(void)+00000004)
004069FB Module(sqlservr+000069FB) (setkeys(class Sarg *,int,class Record &)+00000075)
0080F65C Module(sqlservr+0040F65C) (prepscan(class SDES *,class LinkedPageSupplier *)+00000085)
00407B32 Module(sqlservr+00007B32) (LinkedPageSupplier::FirstPage(class SDES *,class CAutoLatch *)+00000044)
004077A6 Module(sqlservr+000077A6) (LinkedPageSupplier::GetNextPage(class SDES *,int,class CAutoLatch *)+000000DF)
00406E62 Module(sqlservr+00006E62) (SDES::GetBiDi(int,int)+000001A3)
00801327 Module(sqlservr+00401327) (RowsetSS::GotoMarker(int)+000000C1)
0070D1A5 Module(sqlservr+0030D1A5) (CQScanRange::GotoMarker(int,unsigned long *,int)+000000D0)
0070B52C Module(sqlservr+0030B52C) (CQScanNLJoin::GotoMarker(int,unsigned long *,int)+0000030A)
00700998 Module(sqlservr+00300998) (CQScanFilter::GotoMarker(int,unsigned long *,int)+0000001A)
006F7D9F Module(sqlservr+002F7D9F) (CQueryScan::GotoMarker(int,unsigned long *,int)+0000002D)
005D5E75 Module(sqlservr+001D5E75) (CCursorDynaQueryScan::GotoMarker(int,int)+0000006C)
005D4CF8 Module(sqlservr+001D4CF8) (CFetchDynamic::Fetch(long,long,class CFetchIntoVars const *,class CCursorExecContext *,int *,enum CRS::FetchType,long,int,long *,class CAutoShrink *)const +00000311)
005BC5BD Module(sqlservr+001BC5BD) (CCursor::Fetch(enum CRS::FetchType,long,long,class CFetchIntoVars const *,class CMsqlExecContext *,int *,int,int,int,int)+00000257)
005B9617 Module(sqlservr+001B9617) (CCursorFetchStmt::XretExecute(class CMsqlExecContext *)const +0000010E)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)
7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)
77E837CD Module(KERNEL32+000037CD) (TlsSetValue+000000F0)
					


Short Stack Dump
0042C2C1 Module(sqlservr+0002C2C1) (CQScanRange::SetNewRange(unsigned long *)+0000001C)
0070D140 Module(sqlservr+0030D140) (CQScanRange::GotoMarker(int,unsigned long *,int)+00000068)
0070B52C Module(sqlservr+0030B52C) (CQScanNLJoin::GotoMarker(int,unsigned long *,int)+0000030A)
00700998 Module(sqlservr+00300998) (CQScanFilter::GotoMarker(int,unsigned long *,int)+0000001A)
006F7D9F Module(sqlservr+002F7D9F) (CQueryScan::GotoMarker(int,unsigned long *,int)+0000002D)
005D5E75 Module(sqlservr+001D5E75) (CCursorDynaQueryScan::GotoMarker(int,int)+0000006C)
005D4CF8 Module(sqlservr+001D4CF8) (CFetchDynamic::Fetch(long,long,class CFetchIntoVars const *,class CCursorExecContext *,int *,enum CRS::FetchType,long,int,long *,class CAutoShrink *)const +00000311)
005BC5BD Module(sqlservr+001BC5BD) (CCursor::Fetch(enum CRS::FetchType,long,long,class CFetchIntoVars const *,class CMsqlExecContext *,int *,int,int,int,int)+00000257)
005B9617 Module(sqlservr+001B9617) (CCursorFetchStmt::XretExecute(class CMsqlExecContext *)const +0000010E)
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E)
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7)
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343)
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8)
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)
7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)
77E837CD Module(KERNEL32+000037CD) (TlsSetValue+000000F0)
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB281346
©2003 Microsoft Corporation. All rights reserved.