SQL Stored Procedure Problem

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

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:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved