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 < @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 ' +@dbname                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.

See also  Redefining Energy Management: Advanced Solutions for Modern Buildings
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist