Cursor Causing Problem in Sql Serever 2005

Question

Post reply

Cursor Causing Problem in Sql Serever 2005

chinn

SSCrazy

Points: 2840
More actions
August 23, 2010 at 11:13 am

#228257

Hello,
I have a situation where the cursor is not completely executing all the results coming from the Select statement.
I have a Select statement from sys.databases to get the name of the databases and for each database do a backup it works fine.
But i have 35 db's it's only doing for 20 databases and exiting without any error.
Are there any limitations.
Any help on this is appreciated.
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

show us the script you are using...are you tal\king into consderation non-standard database names?

ie select * from [My Database With Spaces Or a Reserved Word].dbo.sys.objects?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chinn SSCrazy Points: 2840 More actions · Answer 2

DECLARE complex_cursor CURSOR FORWARD_ONLY FOR

SELECT name from master..sysdatabases where name not in('Tempdb')

order by name

Open complex_cursor;

FETCH next from complex_cursor into @dbname

WHILE @@fetch_status = 0

BEGIN

set @dbname = upper(@dbname)

set @backupname = @dbname + '_ FULL Backup'

set @backupdesc = @dbname + ' FULL Backup Using LiteSpeed '

EXEC @result = master.dbo.xp_backup_database

@database= @dbname

,@tsmobject= @tsmobject1

, @tsmconfigfile= @stsmconfigfile

,@backupname = @backupname ,@desc = @backupdesc

, @init= 1 ,@tsmxmlcachesize= 100000000 , @with = 'stats = 10'

If @result <> 0

RAISERROR ('Backup Failed',16,1)

FETCH next from complex_cursor into @dbname

END

CLOSE complex_cursor;

DEALLOCATE complex_cursor;

WayneS SSC Guru Points: 95460 More actions · Answer 3

chinn (8/23/2010)
DECLARE complex_cursor CURSOR FORWARD_ONLY FOR
SELECT name from master..sysdatabases where name not in('Tempdb')
order by name
Open complex_cursor;
FETCH next from complex_cursor into @dbname
WHILE @@fetch_status = 0
BEGIN
set @dbname = upper(@dbname)
set @backupname = @dbname + '_ FULL Backup'
set @backupdesc = @dbname + ' FULL Backup Using LiteSpeed '
PRINT @dbname
EXEC @result = master.dbo.xp_backup_database
@database= @dbname
,@tsmobject= @tsmobject1
, @tsmconfigfile= @stsmconfigfile
,@backupname = @backupname ,@desc = @backupdesc
, @init= 1 ,@tsmxmlcachesize= 100000000 , @with = 'stats = 10'
If @result <> 0
RAISERROR ('Backup Failed',16,1)
FETCH next from complex_cursor into @dbname
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

Add the line I put in bold above, and tell us what you get for output.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

chinn SSCrazy Points: 2840 More actions · Answer 4

I did add that code and it prints all the dbnames correctly but it doesnt execute the backup command after it does back for some databases...

It's not erroring out..it just quits...saying it completed successfully.

Is there a way i can rewrite this using if else or while ...

Thanks...

frodriguez.im SSC Enthusiast Points: 184 More actions · Answer 5

Try executing master.dbo.xp_backup_database on QA for one of the databases that is not being backup and see what you get.

frodriguez.im SSC Enthusiast Points: 184 More actions · Answer 6

Also if that returns without errors and doesn't backup the database then you can try running a BACKUP DATABASE command on the same database, if that also fails it will give you a detailed error, if it succeeds then you'll know that the problem is with LiteSpeed so you can seek support from them.

chinn SSCrazy Points: 2840 More actions · Answer 7

I did try all of these...they are working..it's quitting as of there is some limitation some where..i am thinking at present on the tape..but i cant prove that because there is no error..

May be i can backup to disk and see what happens....

Thanks For Your Help!!!