Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
May 23, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

SQL Stored Procedure Problem

I am writing a SQL statement that dynamically dumps the databases on the SQL servers. However, when I run the procedure as a stored procedure it falls out of the WHILE loop and doesn't update the cursor:
DECLARE     @counter         int,
            @db_count        int,
            @dbname          char(30),
            @rename_file     sysname

DECLARE dbname_cursor CURSOR for
  select name from master..sysdatabases where name <> "tempdb" and
         name <> "pubs" and 
         name <> "model"  and 
         name <> "msdb"

    select @db_count = count(*) from master..sysdatabases where 
         name <> "tempdb" and 
         name <> "pubs" and 
         name <> "model"

    select @counter = 0

        exec master..xp_cmdshell 'del e:\dumps\*.old /q /f'
        exec master..xp_cmdshell 'ren e:\dumps\*.* *.old'

        OPEN dbname_cursor

            while @counter < @db_count

                FETCH NEXT FROM dbname_cursor into @dbname
                dump database @dbname to disk= "e:\dumps\temp"
                select @rename_file='ren e:\dumps\temp ' +@dbname
                exec master..xp_cmdshell @rename_file
                select @counter = @counter +1
                select @@fetch_status


CLOSE dbname_cursor
DEALLOCATE dbname_cursor

I ran this code and the only problem I found was that your @counter variable is set to 0, whereas the count of databases would start with one. So the code tries and dumps the last databases twice and generates an error. This is because if the number of databases you have is 3 this line of code

while @counter < @db_count
will cause the code to loop 4 times instead of 3.

Changing the line "select @counter = 0" to "select @counter = 1" causes the backups to work with no errors.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date