Problem with NOLOCK on SQL 2008 using a temporary table and select statements

Question

I am using SQL 2008 and I am getting an error most of the time I run a stored procedure. There error is: could not continue scan with nolock due to data movement. I have looked online for this and it seems that most people get this when updating their SQL version or when doing updates/deletes. I am not doing either. Also the solution seems to be to remove the NOLOCK yet I am not using NOLOCK (unless it is default in SQL Server 2008). I have also tried to insert SET TRANSACTION ISOLATION LEVEL READ uncommitted yet it hasn't made any changes.

My stored procedure consists of getting parameters, creating a temporary table, filling the table with SELECT statements only and then fetching information from the table.

Does anyone know what is wrong?

Adding the stored procedure code might help.

Numenor
– Numenor

2011-04-01 16:04:55 +00:00
Commented Apr 1, 2011 at 16:04 — Numenor
– Numenor, Commented Apr 1, 2011 at 16:04
Kyra: can you post a code sample for us to look at?

Ryan Crowley
– Ryan Crowley

2011-04-01 16:28:27 +00:00
Commented Apr 1, 2011 at 16:28 — Ryan Crowley
– Ryan Crowley, Commented Apr 1, 2011 at 16:28
First time when it's not my bug :)))

Elastep
– Elastep

2012-12-05 10:02:25 +00:00
Commented Dec 5, 2012 at 10:02 — Elastep
– Elastep, Commented Dec 5, 2012 at 10:02

gbn · Accepted Answer · 2011-04-01 18:24:17Z

6

There's a patch for that in SQL server 2008: FIX: You receive error 605 and error 824 when you run a query that inserts data into a temporary table in SQL Server

(fixed link. oops)

edited Apr 1, 2011 at 18:24

answered Apr 1, 2011 at 17:54

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with NOLOCK on SQL 2008 using a temporary table and select statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related