Results 1 to 2 of 2

Thread: cursor

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: cursor

    can someone help me wit this. I'm writing a cursor but getting errors.




    DECLARE cur_objc CURSOR FOR
    SELECT name FROM MASTER..sysdatabases WHERE name NOT IN
    ('tempdb', 'master', 'model', 'msdb', 'sysdb',
    'ReportServer$TEST04', 'ReportServer$TEST04TempDB' )
    GO
    DECLARE @dbname VARCHAR(50)
    declare @sql VARCHAR(8000)

    OPEN cur_objc
    FETCH cur_objc

    while (@@sqlstatus = 0)


    BEGIN
    SET @sql = 'SELECT name FROM ' + @dbname + '..sysobjects'
    print (@sql)


    FETCH @dbname
    END

    CLOSE cur_objc

    DEALLOCATE cur_objc




    Server Message: Number 563, Severity 16
    Server 'DEV_WHSE', Line 1:
    There is already another cursor with the name 'cur_objc' at the nesting level '0'.
    Server Message: Number 102, Severity 15
    Server 'DEV_WHSE', Line 12:
    Incorrect syntax near '@sql'.
    Server Message: Number 102, Severity 15
    Server 'DEV_WHSE', Line 20:
    Incorrect syntax near 'cur_objc'.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    With an error in the 2nd batch the cursor is not de-allocated
    Run only the 2nd batch (after go) or de-allocate the cursor first

    FYI: Here is code not using a cursor to do something similar
    Code:
    select name into #x from sysdatabases
    where name not like 'm%'
    declare @a varchar(16000)
    update #x set @a=isnull(@a,'')+'select '+name+'=count(*) from '+name+'..sysobjects
    go'+'
    '
    exec (@a)
    drop table #x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •