Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 SP2 Cursor does not complete Expand / Collapse
Author
Message
Posted Saturday, December 15, 2007 12:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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?
Post #433697
Posted Saturday, December 15, 2007 2:06 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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
Posted Saturday, December 15, 2007 2:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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
Posted Saturday, December 15, 2007 4:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse