SQL Server 2005 SP2 Cursor does not complete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Server 2005 SP2 Cursor does not complete

SQL Server 2005 SP2 Cursor does not complete

Rate Topic

Display Mode

Topic Options

Author

Message

Les Sprott

Posted Saturday, December 15, 2007 12:28 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 05, 2009 7:17 AM
Points: 19, Visits: 45

I am searching for help with a cursor that does not complete. it gives no errors and is random. When I run the SQL with commands commented out and view the results of the cursor it rolls through the data exactly like I want it to. when I uncomment the commands it executes it does 1/2 and then ends with success?

Has anyone else seen this behavior and know if there is a fix? Hehe

Post #433697

einman33

Posted Saturday, December 15, 2007 2:06 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512

Can you post source?

Post #433701

timothyawiseman

Posted Saturday, December 15, 2007 2:15 PM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 748, Visits: 905

Can you provide more detail? Its hard to say much based on this so far.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #433703

Les Sprott

Posted Saturday, December 15, 2007 4:49 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 05, 2009 7:17 AM
Points: 19, Visits: 45

The instance has eight databases and only backs up the first four and the jobs ends with no errors. it just does half of the databases in the cursor.

SET NOCOUNT ON
go
SET QUOTED_IDENTIFIER OFF
go
USE master
go

declare db_list_csr cursor for
select name
from sysdatabases
where name not in ('tempdb','Northwind','pubs')
order by name
go

declare @had_error bit
declare @currtime char(19)

declare @dbname varchar(45)
declare @file0 varchar(45), @file1 varchar(45), @file2 varchar(45)
declare @msg varchar(255)

/* Initialize stuff */
select @had_error = 0

open db_list_csr
fetch next from db_list_csr into @dbname
while @@fetch_status=0
begin
select @file0="g:\DBA\Backups\"+@dbname+".bak"

select @currtime = getdate()
select @msg = "Start dumping database "+@dbname+" at "+@currtime
print @msg

backup database @dbname to disk= @file0

if @@error = 0
begin
select @msg = "Database dump of "+@dbname+" completed at "+@currtime
print @msg
end
else
begin
select @had_error = 1
print "**********************************************************"
print "**********************************************************"
select @msg = "Database dump of "+@dbname+" failed at "+@currtime
print @msg
print "**********************************************************"
print "**********************************************************"
end
fetch next from db_list_csr into @dbname
end
close db_list_csr
deallocate db_list_csr

if @had_error = 1
goto error
else
print "That's all!"
return

error:

print "*******************************"
print "* DATABASE DUMPS HAD ERRORS *"
print "*******************************"
return
go

Post #433715

« Prev Topic | Next Topic »

Permissions