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     sysnameDECLARE 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	emp"                select @rename_file='ren e:dumps	emp ' [email protected]                exec master..xp_cmdshell @rename_file                select @counter = @counter +1                select @@fetch_status            ENDCLOSE dbname_cursorDEALLOCATE 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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: