Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

SQL Stored Procedure Problem

Question:
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

            BEGIN
                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

            END

CLOSE dbname_cursor
DEALLOCATE dbname_cursor

Answer:
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.

 

 

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