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

sql-server-2008 nolock
Kyra picture Kyra · Apr 1, 2011 · Viewed 7.2k times · Source

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?

Answer

gbn picture gbn · Apr 1, 2011

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)

Related questions

Is the NOLOCK (Sql Server hint) bad practice?

I'm in the business of making website and applications that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea. So, with that massive disclaimer, is it bad using the …

Read More
with(nolock) , (nolock) , nolock differences?

I know with(nolock) and (nolock) are the same or almost the same. REF:with(nolock) or (nolock) - Is there a difference? but how about nolock? You can use either of them in select and the only notable difference …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More