Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-07-29 : 06:22:59
|
Hi All,Need help with below query.there are 10 statements in the querywhen i execute the query ,if suppose the first statement fails to execute i want it to continue executing the other statements.how can i do that.select a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S001WS\SQLEXPRESS ' FROM [S001WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S001WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S001WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S002WS\SQLEXPRESS ' FROM [S002WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S002WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S002WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S003WS\SQLEXPRESS ' FROM [S003WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S003WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S003WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S004WS\SQLEXPRESS ' FROM [S004WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S004WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S004WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S005WS\SQLEXPRESS ' FROM [S005WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S005WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S005WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S006WS\SQLEXPRESS ' FROM [S006WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S006WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S006WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S007WS\SQLEXPRESS ' FROM [S007WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S007WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S007WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S008WS\SQLEXPRESS ' FROM [S008WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S008WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S008WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S009WS\SQLEXPRESS ' FROM [S009WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S009WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S009WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameThanks,Javeed.mohammad.javeed.ahmed@gmail.com |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-29 : 06:24:32
|
add a GO between them and execute so that each statement will be in its own batch and will run regardless any error in the other------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-29 : 06:25:17
|
ie likeselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S001WS\SQLEXPRESS ' FROM [S001WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S001WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S001WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameGOselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S002WS\SQLEXPRESS ' FROM [S002WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S002WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from [S002WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameGOselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S003WS\SQLEXPRESS ' FROM [S003WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S003WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S003WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameGOselect a.name, b.backup_finish_date as LastBackupDate,CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, 'S004WS\SQLEXPRESS ' FROM [S004WS\SQLEXPRESS].master.dbo.sysdatabases a left join[S004WS\SQLEXPRESS].msdb.dbo.backupset b on a.name = b.database_name where b.backup_finish_date = (select max(backup_finish_date) from[S004WS\SQLEXPRESS].msdb.dbo.backupset where database_name = a.name and type = 'D') and a.name not in('Admin') order by a.nameGO... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-07-29 : 06:28:35
|
Thanks Visakh.It workedmohammad.javeed.ahmed@gmail.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-29 : 06:55:07
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|