December 15, 2007 at 12:28 pm
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:
December 15, 2007 at 2:06 pm
Can you post source?
December 15, 2007 at 2:15 pm
Can you provide more detail? Its hard to say much based on this so far.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 15, 2007 at 4:49 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply