devxlogo

SQL Stored Procedure Problem

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 

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 

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-admin

Share the Post: