Cursor does not exists - 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

General SQL Server Forums

New to SQL Server Administration

Cursor does not exists

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-24 : 23:46:46

Hi All,,

I want to shrink the databases if they are online and in simple recovery mode i have written a store stored procedure but the problem is when i execute it it says that cursor does not exists..Here is the code

CREATE PROCEDURE SRINKDATABASE
AS

CREATE TABLE #TEMP
( DBNAME VARCHAR(50),
STATUS VARCHAR(20),
UPDATEABILITY VARCHAR(50),
RECOVERY VARCHAR(10))

INSERT INTO #TEMP SELECT A.DBNAME,A.STATUS,A.UPDATEABILITY,A.RECOVERY FROM (
SELECT DBname=name,
Status= CONVERT(VARCHAR(20),DatabasePropertyEx(name,'Status')),
Updateability= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Updateability')),
UserAccess= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'UserAccess')),
Recovery= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Recovery')),
Version= CONVERT(VARCHAR(10),DatabasePropertyEx(name,'Version'))
from sysdatabases ) A WHERE A.DBNAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')

DECLARE @CUR_NM VARCHAR(50)
DECLARE @CUR_STAT VARCHAR(20)
DECLARE @CUR_UPDT VARCHAR(50)
DECLARE @CUR_REC VARCHAR(10)
DECLARE @MSG VARCHAR(100)

DECLARE CHKDATABASE_CUR CURSOR FOR
SELECT DBNAME,STATUS,RECOVERY FROM #TEMP

OPEN CHKDATABASE_CUR

FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@CUR_STAT = 'ONLINE' AND @CUR_REC = 'SIMPLE')
BEGIN
DBCC SHRINKDATABASE (@CUR_NM, TRUNCATEONLY)
SET @MSG = 'NAME OF THE DATABSE SHRINKED IS ' + @CUR_NM
PRINT @MSG
FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
END
CLOSE CHKDATABASE_CUR
Deallocate CHKDATABASE_CUR
END

Please help me to dig it out.Below is the error maessage it is keep giving.

Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-25 : 13:51:36

The problem is in framing Begin and End Block.

Try this:

Create PROCEDURE SRINKDATABASE
AS
Begin
CREATE TABLE #TEMP
( DBNAME VARCHAR(50),
STATUS VARCHAR(20),
UPDATEABILITY VARCHAR(50),
RECOVERY VARCHAR(10))

INSERT INTO #TEMP SELECT A.DBNAME,A.STATUS,A.UPDATEABILITY,A.RECOVERY FROM (
SELECT DBname=name,
Status= CONVERT(VARCHAR(20),DatabasePropertyEx(name,'Status')),
Updateability= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Updateability')),
UserAccess= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'UserAccess')),
Recovery= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Recovery')),
Version= CONVERT(VARCHAR(10),DatabasePropertyEx(name,'Version'))
from sysdatabases ) A WHERE A.DBNAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')

DECLARE @CUR_NM VARCHAR(50)
DECLARE @CUR_STAT VARCHAR(20)
DECLARE @CUR_UPDT VARCHAR(50)
DECLARE @CUR_REC VARCHAR(10)
DECLARE @MSG VARCHAR(100)

DECLARE CHKDATABASE_CUR CURSOR FOR
SELECT DBNAME,STATUS,RECOVERY FROM #TEMP

OPEN CHKDATABASE_CUR

FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@CUR_STAT = 'ONLINE' AND @CUR_REC = 'SIMPLE')
BEGIN
DBCC SHRINKDATABASE (@CUR_NM, TRUNCATEONLY)
SET @MSG = 'NAME OF THE DATABSE SHRINKED IS ' + @CUR_NM
PRINT @MSG
End
FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
END
CLOSE CHKDATABASE_CUR
Deallocate CHKDATABASE_CUR
END

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-04-25 : 16:01:02

Why do you want to shrink the databases? Even using truncate only - that will only remove empty space from the end of the data file but will shrink the log file, which will just have to grow again.

I would not recommend doing this at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources