cursor query does not work

Question

cursor query does not work

Heidi Wood

Valued Member

Points: 73
More actions
October 5, 2004 at 5:43 pm

#161641

Hi all,
Can anyone tell me why this does not work?
Below is the following error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object '"DC_Image.DC\jhcrawfo.A210530"' does not exist or is not a valid object for this operation.
It is a valid object and it does exist.

Below is the saved query:
use dc_image
DECLARE prob_cursor CURSOR FOR
select '"DC_Image.' + u.name + '.' + o.name + '"' as anobject from sysobjects o
JOIN sysusers u ON u.UID = o.UID
where o.xtype = 'U' and o.uid <> 1
order by o.name
OPEN prob_cursor
-- Perform the first fetch.
Declare @objectname nvarchar(517)
FETCH NEXT FROM prob_cursor
into @objectname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
DECLARE @newowner nvarchar(128)
SELECT @newowner = 'dbo'
EXEC sp_changeobjectowner @objectname , @newowner
--to confirm
DECLARE @PrnLine nvarchar(4000)
SELECT @PrnLine = 'Object ' + @objectname + ' has been fixed to '+ @newowner
PRINT @PrnLine

   FETCH NEXT FROM prob_cursor
   into @objectname
END
CLOSE prob_cursor
DEALLOCATE prob_cursor
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

R M Buda SSCrazy Points: 2225 More actions · Answer 1

Not sure if this is it but, your cursor should be declared STATIC since the underlying data is being modified during execution. STATIC ensures a buffered copy is used for the cursor loop.

Try:

DECLARE prob_cursor CURSOR STATIC LOCAL FOR ...

vadba SSChampion Points: 11133 More actions · Answer 2

I made some slight modifications, and it works for me. Note that when using variables with this stored procedure, you don't need quotes around object names. Also, an INSENSITIVE cursor works well, too.

DECLARE prob_cursor INSENSITIVE CURSOR

FOR SELECT 'TEST.' + u.name + '.' + o.name AS anobject

FROM sysobjects o JOIN sysusers u ON u.UID = o.UID

WHERE o.xtype = 'U' AND o.uid <> 1

ORDER BY o.name

OPEN prob_cursor

-- Perform the first fetch.

DECLARE @objectname nvarchar(517)

DECLARE @newowner nvarchar(128)

DECLARE @PrnLine nvarchar(4000)

FETCH NEXT FROM prob_cursor INTO @objectname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

-- This is executed as long as the previous fetch succeeds.

SELECT @newowner = 'dbo'

EXEC sp_changeobjectowner @objectname , @newowner

--to confirm

SELECT @PrnLine = 'Object ' + @objectname + ' has been fixed to '+ @newowner

PRINT @PrnLine

FETCH NEXT FROM prob_cursor INTO @objectname

END

CLOSE prob_cursor

DEALLOCATE prob_cursor

GO

Heidi Wood Valued Member Points: 73 More actions · Answer 3

Thank you so much ah...Grasshopper and Enthusiast!!!!

I knew it was just a little tweak that I couldn't see.