A cursor with the name 'TableCursor' already exist

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

Transact-SQL (2000)

A cursor with the name 'TableCursor' already exist

Author

Topic

dhar4sqlteam
Starting Member

3 Posts

Posted - 2008-04-26 : 03:13:54

Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'TableCursor' already exists.

I wrote a script which will recreate all the indexes for the given database. When I run for aroun 300 tables it took 2 hours but gave the above error for all the tables!

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name IN ('Bank1')
--WHERE name IN ('RS_ATICNFS_MIG')
--WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000 command
DBCC DBREINDEX(@Table, ' ', @fillfactor)
--print @Table

-- SQL 2005 command
--SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 03:26:11

I think its because you are trying to declare cursor inside loop. Can you post full query of what you're trying to achieve?

dhar4sqlteam
Starting Member

3 Posts

Posted - 2008-04-26 : 03:32:16

my goal is to reindex all the tables on one database.

The code is just same. apart from the mentioned 4 databases, I have one more DB that I wanted to reindex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 03:52:46

I have tried to rewrite your query using while loop instead of cusrsor. Try this and let me know how to got on

DECLARE @Database VARCHAR(255) 
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT 

SET @fillfactor = 90 


SELECT @Database=MIN(name)
FROM master.dbo.sysdatabases 
WHERE name IN ('Bank1')

WHILE @Database IS NOT NULL
BEGIN 

SET @cmd ='SELECT @Table=MIN(table_catalog + ''.'' + table_schema + ''.'' + table_name)
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 


-- create table cursor 
EXEC (@cmd) 
 


WHILE @Table IS NOT NULL
BEGIN 

-- SQL 2000 command 
DBCC DBREINDEX(@Table, ' ', @fillfactor) 
--print @Table

-- SQL 2005 command 
--SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
EXEC (@cmd) 

SET @cmd ='SELECT @Table=MIN(table_catalog + ''.'' + table_schema + ''.'' + table_name)
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'' AND table_catalog + ''.'' + table_schema + ''.'' + table_name>@Table' 

EXEC (@cmd)
END 

SELECT @Database=MIN(name)
FROM master.dbo.sysdatabases 
WHERE name IN ('Bank1')
AND name > @Database
END

erdem12345
Starting Member

8 Posts

Posted - 2008-04-26 : 04:00:46

while in EXEC (@cmd) to cancel..

-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@Table, ' ', @fillfactor)

--EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

dhar4sqlteam
Starting Member

3 Posts

Posted - 2008-04-26 : 04:47:40

the whole stuff can be rewritten using

use myDB
sp_MSforeachtable @command1="print '?' dbcc DBREINDEX ('?', ' ', 90)"

I just started running this script; will update once its thru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources