Could not continue scan with NOLOCK due to data movement

Question

Post reply

Could not continue scan with NOLOCK due to data movement

hot2use

SSCrazy

Points: 2915

May 8, 2006 at 3:08 am

#172429

The error message:

Server: Msg 601, Level 12, State 6, Line 1

Could not continue scan with NOLOCK due to data movement.

We have an intranet solution running on Stellent and have a

non-Stellent solution for notifying users of updated content.

In order to release an e-mail for updated content we have

created some stored procedures.

Because an input document (CMSdDocName) can have multiple

output documents (dDocName) we only want to notify the user

once for each updated content and not for each output file itself.

The sproc iterates through a temp table which is populated with

a list of documents that where updated since the last time the

sproc was run.

All duplicate entries for the output documents (CMSdDocName)

should be reduced to one entry, ensuring that the user/subscriber

is notified only once.

Some times the sproc runs successfully and other times it fails with

the above error message. I have narrowed the error down to the

following part of the sproc by entering debug/print statements.

SPROC SNIPPET
[...]
Select @dLastNotify = convert(dateTime, sValue) from xCsoConfig where sName = 'dLastSubscriptionUpdate'

insert into #TempNewDok (dDocName, dID, CMSdDocName) 
    select R.dDocName, R.dID, D.xDocName from Revisions R
    Inner join DocMeta D on R.Did = D.Did Inner join CMS.dbo.DocumentHistory dh on D.xDocRevisionID = dh.did
    where R.dReleaseDate Between @dLastNotify and @DateTarget and (dh.dAction = 'Checkin' and dactiondate > @dLastNotify)
    and D.xPageNumber = 0 and R.dDocName not like 'ni_%' and R.dDocName not like 'bn_%' and R.dDocType not like 'web_graphic'

-- Über Dokumente von der gleichen CMS-ID nur einmal informieren
declare dup_cur cursor local 
for select CMSdDocName,count(*) from #TempNewDok group by CMSdDocName having count(*) > 1
    open dup_cur
        fetch next from dup_cur into @schluessel,@anz
        while (@@fetch_status = 0)
        begin
 set @anz = @anz - 1
 select @vSQL = 'DELETE #TempNewDok FROM 
(SELECT TOP ' + convert(varchar, @anz) + ' * 
FROM #TempNewDok with (nolock) where CMSdDocName = ''' + @schluessel + ''')  
AS t1 WHERE #TempNewDok.dId = t1.did'
 print 'before executing script: '
 print @vSQL
 Execute (@vSQL)
 print 'after executing script: '
 print @vSQL
   Set @anz = 0
      fetch next from  dup_cur into @schluessel,@anz
        end
close dup_cur
deallocate dup_cur
[...]

In instances where the sproc fails the temp table #TempNewDok contains content similar to this:

TABLE #tempnewdok

ID          dDocName                               dID         CMSdDocName             
----------- -------------------------------------- ----------- ------------------------
1           id_4244DE                              803634      4244DE
2           id_4243DE                              804054      4243DE
3           id_805EN                               804074      805EN
4           id_805DE                               804075      805DE
5           id_3950DE                              805538      3950DE
6           id_3952DE                              805990      3952DE
7           id_3952EN                              805991      3952EN
8           id_23271DE                             806058      23271DE
9           id_15645DE                             806238      15645DE
10          id_15645EN                             806239      15645EN
11          id_494DE                               806245      494DE
12          id_17688DE                             811307      17688DE
13          id_17692DE                             812393      17692DE
14          id_17683DE                             812394      17683DE
15          id_17696DE                             813028      17696DE
16          Frm_BUDD_CT_TNODE                      814102      Frm_BUDD_CT_TNODE
17          Frm_buifs_pcbestordde                  814331      Frm_buifs_pcbestordde
18          sc_10850DE                             816603      10850DE
19          sc_10850EN                             816604      10850EN
20          sc_11997DE                             816722      11997DE
21          sc_12883DE                             816725      12883DE
22          sc_5353DE                              816789      5353DE
23          sc_Edo_rgel1en                         816960      Edo_rgel1en
24          sc_Edo_rgel1de                         816961      Edo_rgel1de
25          sc_Edo_mddoen                          816990      Edo_mddoen
26          sc_Edo_mddode                          816991      Edo_mddode
27          sc_Edo_rgel4de                         817041      Edo_rgel4de
28          sc_Edo_rgel4en                         817086      Edo_rgel4en
29          sc_Edo_mddmen                          817120      Edo_mddmen
30          sc_Edo_mddmde                          817123      Edo_mddmde
31          sc_Edo_mmujen                          818060      Edo_mmujen
32          sc_Edo_mmujde                          818061      Edo_mmujde
33          sc_27771EN                             818064      27771EN
34          sc_27771DE                             818065      27771DE
35          sc_Edo_mfvhen                          818085      Edo_mfvhen
36          sc_Edo_mfvhde                          818086      Edo_mfvhde
37          sc_Edo_mvsqde                          818133      Edo_mvsqde
38          sc_Edo_mvsqen                          818134      Edo_mvsqen
39          sc_Edo_msdgde                          818206      Edo_msdgde
40          sc_Edo_msdgen                          818207      Edo_msdgen
41          sc_28422EN                             818208      28422EN
42          sc_28422DE                             818209      28422DE
43          sc_19173DE                             818224      19173DE
44          sc_19171DE                             818225      19171DE
45          Aut_HW005DE                            826030      Aut_HW005DE
46          20960EN                                827486      20960EN
47          20960DE                                827487      20960DE
48          19173DE                                829377      19173DE
49          sc_29244DE                             832078      29244DE
50          sc_29244EN                             832079      29244EN
51          sc_30319DE                             833820      30319DE
52          3952DE                                 834699      3952DE
53          3952EN                                 834700      3952EN

With the above data, the delete statement in the sproc executed

by the Execute (@vSQL) part fails while trying to delete the

recordset with ID 43.

The statement executed is:

DELETE #TempNewDok FROM (SELECT TOP 1 * FROM #TempNewDok with (nolock) 
where CMSdDocName = '11791DE')  AS t1 WHERE #TempNewDok.dId = t1.did

My questions are:

1. What am I overlooking?

2. Are there any syntax errors in the statement?

3. Does the cursor dup_cur for deleting duplicate entries have

to be defined differently?

I do realize that I am trying to manipulate data which is being

read by the dup_cur cursor, but I am unsure how to overcome

the issue.

I have tried defining the cursor with STATIC and READ_ONLY,

but even then the sproc fails to execute correctly.

Thanks for your feedback anyway. If you need more info let me know.

- hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Try:

insert into #TempNewDok (dDocName, dID, CMSdDocName)

select D.Did

,D.xDocName

from DocMeta D

join Revisions R on D.Did = R.Did

join CMS.dbo.DocumentHistory dh on D.xDocRevisionID = dh.did

where D.Did = (select max(D1.Did)

from DocMeta D1

where D1.xDocName = D.xDocName)

and R.dReleaseDate Between @dLastNotify and @DateTarget

and dh.dAction = 'Checkin'

and dactiondate > @dLastNotify

and D.xPageNumber = 0

and R.dDocName not like 'ni_%'

and R.dDocName not like 'bn_%'

and R.dDocType <> 'web_graphic'

hot2use SSCrazy Points: 2915 More actions · Answer 2

Hi Ken

Thanks for your feedback. If I understand your statement correctly,

the sproc would be populating the temp table only with the maximum

values for each given dID?

I wasn't looking for the real cause of the problem at the beginning and

came up with the following quick 'n dirty solution:

select * into #TempNewDok2 from #TempNewDok
-- Inserts the duplicate data into an identical table

-- Über Dokumente von der gleichen CMS-ID nur einmal informieren
declare dup_cur cursor local
for select CMSdDocName,count(*) from #TempNewDok2 group by CMSdDocName having count(*) > 1
    open dup_cur
        fetch next from dup_cur into @schluessel,@anz
        while (@@fetch_status = 0)
        begin
 set @anz = @anz - 1

 -- here i delete the duplicate data from the temp table using the exact twin
select @vSQL = 'DELETE #TempNewDok FROM 
(SELECT TOP ' + convert(varchar, @anz) + ' * 
FROM #TempNewDok2 where CMSdDocName = ''' + @schluessel + ''')  
AS t1 WHERE #TempNewDok.dId = t1.did'

 Execute (@vSQL)
   Set @anz = 0
      fetch next from  dup_cur into @schluessel,@anz
        end
close dup_cur
deallocate dup_cur

Because I was looking for a quick solution (productive environment,

not my code) it didn't spring to mind that populating the temp

table with only the required data would be the better solution.

Cheers. I'll give it a spin and see what happens. Will let you know.

- hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.