SQL Server Cursor Limitation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server Cursor Limitation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-20 : 07:48:10

john writes "I am performing the following query on a table with 37,000 records. Each time I run the query, the query stops after 32,736 rows. I tried this 5 times and also checked the value of the range and it is OK. Any help would be appreciated.
Thanks

declare @NEXTOID int, @REFILL bit, @OIDPOOL int
declare @i bigint;
declare @RC bigint;
declare @NOID bigint;
Set @i=0
SET @NEXTOID=1000000
SET @RC = 37000
set @NOID = @NEXTOID
Declare mycursor cursor
for select * from FacelessWorkers1
Open myCursor
PRINT @RC
FETCH NEXT from mycursor
while @i < @RC
Begin
SET @NOID = @NOID+1
Print @i
Print @NOID
Update FacelessWorkers1 set OID=@NOID where current of mycursor

SET @i = @i+1
FETCH NEXT from mycursor
END
CLOSE mycursor
deallocate mycursor"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-20 : 12:14:38

1. stay away from cursors...bad news.
2. stay further away from cursors...continue to be bad news....(search here for loads of reasons)

You should be able to rewrite this using a set-based technique.

Do the following confirm your initial statements?
SELECT count(*) from facelessworkers1
SELECT count(*) from facelessworkers1 where OID <> 0

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-20 : 17:38:34

Will this do it for you?

--Assuming facelessworkers1_ID is the primary key of table facelessworkers1.

create table #temp
(
facelessworkers1_ID	int	not null 
			primary key clustered,
New_OID			int	not null identity(1000001,1)
) 

-- Load #temp table with primary key values and identy values
-- starting from first new OID
insert into #temp ( facelessworkers1_ID )
select
	facelessworkers1_ID
from
	facelessworkers1
order by
	facelessworkers1_ID

-- Update OID with values in #temp table
update facelessworkers1
set
	OID = New_OID
from
	facelessworkers1
	join
	#temp a
	on facelessworkers1.facelessworkers1_ID = a.facelessworkers1_ID

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources